# Getting SumIf values with VBA

I have a number of formulas in Column AZ like this:
=SUMIF(AllConfigs,AV6,AllJ)

I'd like to have the option of using VBA to produce the same results. When I run this code below, I get all zeroes.  Is there a way to make this work?

Thanks,
John

``````Sub SumIfs()
Dim cel As Range
For Each cel In Range("AZ6:AZ10")
Dim str As String
cel = Application.WorksheetFunction.SumIf([AllConfigs], str, [AllJ])
Next
End Sub
End Sub
``````
LVL 1
###### Who is Participating?

Commented:
cel.formula = "=sumif(Allconfigs," & str & ",AllJ)"
cel.value = cel.value
0

Solutions ConsultantCommented:
John

If you are using VBA why not just write the logic yourself for the function

The code below get a total for range A1:A20 where the value in the same row for Column B is equal to
the value in C1
``````Sub sumifs()

Dim i As Integer, j As Integer
Dim sumif As Long
Dim test As Long

test = Range("C1").Value

For i = 6 To 10
sumif = 0
For j = 1 To 20
If Range("B" & j).Value = test Then
sumif = sumif + Range("A" & j).Value
End If
Next
Range("AZ" & i).Value = sumif
Next

End Sub
``````
0

Commented:
That's because in VBA code you use the actual ranges not the addresses.
Try
cel = Application.WorksheetFunction.SumIf(Range("AllConfigs"), cel.Offset(0, -4).Text, Range("AllJ"))

I assume cel.Offset(0,-4) contains the condition (don't put quotes around it)
0

Reliability Business Tools Analyst IIAuthor Commented:

Michael, I tried yours as-is and with several attempts at modification and couldn't get it to work. If it does indeed work, pls forgive me for leaving you out.

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.