Printable View
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 :
How can I re-write it so that it only Sums & counts the entries on Column B, and to fill in Column I & J ?Code:Range("F2:F" & Endrow).Formula = "=SUMIF(A:A,E2,B:B)"
See the Attached .xls sheet
you can use "sumifs".
& For those interested, this works
=SUMIFS(B:B,A:A,E2,B:B, ">100.00")
alternative method using array instead of formula
Code: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
mana: Thanks !
Didn't see your post until now. Not sure what happened.
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