I have used SUMIFS before, but have been head banging trying to get this to work.
NOTE: My dataset is filtered. I want to loop through the filtered (visible) rows and extract certain data elements onto another sheet. But I only want to create one row per unique combination of Col E, O, and P. I therefore need to sum Col H based on the other 3 columns matching the criteria.
My data looks like this:
Relevant code is below. You will see that I tried hardcoding the 2nd SUMIFS instead of using variables. No matter what, I get a sum of zero, rather than 3528.
Can anyone spot my error?
NOTE: My dataset is filtered. I want to loop through the filtered (visible) rows and extract certain data elements onto another sheet. But I only want to create one row per unique combination of Col E, O, and P. I therefore need to sum Col H based on the other 3 columns matching the criteria.
My data looks like this:
Col E | Col H | Col O | Col P |
BP | 1953 | 6 | 2020 |
BP | 1575 | 6 | 2020 |
VBA Code:
Sub Extract_Data()
'
Dim iDataLastRow As Integer
Dim sSum As Single
Dim wsData As Worksheet
Dim rSum As Range, rData As Range, rDate As Range
Set wsData = ThisWorkbook.Sheets("Transactions")
iDataLastRow = wsData.Range("A2").End(xlDown).row
Set rData = wsData.Range("A2:A" & iDataLastRow)
Set rSum = wsData.Range("H2:H" & iDataLastRow)
For Each rDate In rData.SpecialCells(xlCellTypeVisible) 'to only look at visible rows
sSum = Application.SumIfs(rSum, _
wsData.Range("O2:O" & iDataLastRow), rDate.Offset(0, 14), _
wsData.Range("P2:P" & iDataLastRow), rDate.Offset(0, 15), _
wsData.Range("E2:E" & iDataLastRow), rDate.Offset(0, 7))
'Ttied hardcoding the criteria and still get a sum of 0
sSum = Application.SumIfs(rSum, _
wsData.Range("O2:O" & iDataLastRow), "6", _
wsData.Range("P2:P" & iDataLastRow), "2020", _
wsData.Range("E2:E" & iDataLastRow), "BP")
Next
End Sub
Can anyone spot my error?