# Thread: Using VBA SUMIF with multiple conditions

1. ## Using VBA SUMIF with multiple conditions

I'm using SUMIF to get the gross for each person and list them separately, but I want to extend it to only Sum the totals over \$100.00 on Column B to put into Column F, and to only count the amounts also over \$100 for Column H.

The VBA portion that Sums the total for Column F is :

`    Range("F2:F" & Endrow).Formula = "=SUMIF(A:A,E2,B:B)"`
How can I re-write it so that it only Sums & counts the entries on Column B, and to fill in Column I & J ?
See the Attached .xls sheet

2. you can use "sumifs".

3. & For those interested, this works

=SUMIFS(B:B,A:A,E2,B:B, ">100.00")

4. alternative method using array instead of formula

```Option Explicit

Sub test()
Dim tbl As Range, v()
Dim dic As Object
Dim i As Long, n As Long, s
Dim m As Long

Set tbl = Cells(1).CurrentRegion
ReDim v(1 To tbl.Count - 1, 1 To 7)

Set dic = CreateObject("scripting.dictionary")

For i = 2 To tbl.Rows.Count
s = tbl(i, 1)
If Not dic.exists(s) Then
dic(s) = dic.Count + 1
v(dic.Count, 1) = s
End If
n = dic(s)
v(n, 2) = v(n, 2) + tbl(i, 2)      'gross
v(n, 4) = v(n, 4) + 1              'count
If tbl(i, 2) > 100 Then
v(n, 5) = v(n, 5) + 1           'Count Over \$100.00
Select Case Year(tbl(i, 3))
Case 2015                    '2015Gross > \$100.00
v(n, 6) = v(n, 6) + tbl(i, 2)
Case 2016                    '2016Gross > \$100.00
v(n, 7) = v(n, 7) + tbl(i, 2)
End Select
End If
Next

m = dic.Count + 2

For i = 1 To m
v(m, 2) = v(m, 2) + v(i, 2)
v(m, 4) = v(m, 4) + v(i, 4)
v(m, 5) = v(m, 5) + v(i, 5)
v(m, 6) = v(m, 6) + v(i, 6)
v(m, 7) = v(m, 7) + v(i, 7)
Next

Columns("e:k").Resize(Rows.Count - 1).Offset(1).ClearContents
Range("e2").Resize(m, 7).Value = v

End Sub```

5. mana: Thanks !

Didn't see your post until now. Not sure what happened.

6. I'm sorry, there was a mistake in my code.