# Help with sumif formula of a range

• ### Question

• Hello All,

I'm trying to create a code that will run on a specific range and will sum up all cells that are different then 0.

first cell is "I11" and last cell is "I44"

Bellow is my code. the result i'm getting is zero no matter what information the cells containing

Thanks for your help:)

Dim bottomCell As Range
Dim currentCell As Range

Dim i As Integer
Dim quantityForThisCode As Integer
Dim CRIT As Integer

i = 11
CRIT = 0

Set bottomCell = Worksheets("Oct All").Range("I" & i).End(xlDown)
Set currentCell = Worksheets("Oct All").Range("I" & i)

Do While i <= 44

If currentCell <> CRIT Then
quantityForThisCode = quantityForThisCode + currentCell.Value
Else
quantityForThisCode = quantityForThisCode
End If
i = i + 1

Loop
Worksheets("Oct All").Range("I45") = quantityForThisCode
End Sub

Thursday, November 10, 2011 10:44 AM

### Answers

• Hi

Why can't you use the worksheet function directly

WorksheetFunction.SUMIF(B:B,"<>0",C:C)

is the equivalent for

=SUMIF(B:B,"<>0",C:C)

which searches for non-zero value in Column B and Sums the corresponding value from Column C

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Proposed as answer by Friday, November 11, 2011 7:32 AM
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Thursday, November 10, 2011 10:59 AM
• Hi

When you use "L11.End(xlDown)" it is becoming a literal and Excel is looking for a range

"L11:L11.End(xlDown)") which it couldn't find and throws an error

You can use the following way

LEnd = Range("L11").End(xlDown).Address
IEnd = Range("I11").End(xlDown).Address
DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & LEnd), "Y", Range("I11" & ":" & IEnd))

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Friday, November 11, 2011 7:34 AM
• Hi

You can try

Worksheets("Sheet1").Range("L11") etc

or

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("L11").value

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Monday, November 14, 2011 6:03 AM

### All replies

• Hi

Why can't you use the worksheet function directly

WorksheetFunction.SUMIF(B:B,"<>0",C:C)

is the equivalent for

=SUMIF(B:B,"<>0",C:C)

which searches for non-zero value in Column B and Sums the corresponding value from Column C

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Proposed as answer by Friday, November 11, 2011 7:32 AM
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Thursday, November 10, 2011 10:59 AM
• i'VE TRIED IT WITH SIMPLE RANGE AND ITS WORKING. tHANKS ALOT.

fOR THE NEXT STEP I WOULD LIKE TO USE IT ON A DYNAMIC RANGE.

tHIS IS MY CODE. i'M GETTING EROR MASSEGE "METHOD RANGE OF OBJECT FAILED"

HERE IS THE CODE

DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & "L11.End(xlDown)"), "Y", Range("I11" & ":" & "I11.End(xlDown)"))

Thursday, November 10, 2011 12:36 PM
• Hi

When you use "L11.End(xlDown)" it is becoming a literal and Excel is looking for a range

"L11:L11.End(xlDown)") which it couldn't find and throws an error

You can use the following way

LEnd = Range("L11").End(xlDown).Address
IEnd = Range("I11").End(xlDown).Address
DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & LEnd), "Y", Range("I11" & ":" & IEnd))

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Friday, November 11, 2011 7:34 AM
• Got you.

Last thing.

how the code know s which sheet to refer? i'm indicating the range but with no referance to the sheet...

I've tried to write before the worksheet.function : Worksheets("sheet1"). activate

But still i'm getting zero from the calculation..

Sunday, November 13, 2011 9:12 AM
• Hi

You can try

Worksheets("Sheet1").Range("L11") etc

or

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("L11").value

Cheers

Shasur

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
• Marked as answer by Saturday, January 28, 2012 5:59 PM
Monday, November 14, 2011 6:03 AM