PDA

View Full Version : [SOLVED] Using VBA SUMIF with multiple conditions



simora
09-30-2016, 07:29 PM
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

mana
09-30-2016, 07:46 PM
you can use "sumifs".

simora
09-30-2016, 09:51 PM
& For those interested, this works

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

mana
09-30-2016, 10:57 PM
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

simora
10-01-2016, 12:19 PM
mana (http://www.vbaexpress.com/forum/member.php?61551-mana): Thanks !

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

mana
10-01-2016, 07:24 PM
I'm sorry, there was a mistake in my code.
Please correct.


wrong: For i = 1 To m
right: For i = 1 To dic.Count