Troubleshooting SUMIFS in VBA

[indyman]

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:

Col E	Col H	Col O	Col P
BP	1953​	6​	2020​
BP	1575​	6​	2020​

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.

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?

 

[bstory84]

On the 3rd criteria, Change the offset column from 7 to 4.

 

[indyman]

On the 3rd criteria, Change the offset column from 7 to 4.

Yes. You are correct. That should be a 4.SO thanks. But it did not resolve the issue.

 

[bstory84]

Works for me. Both statements are returning a value of 3528.
Have you checked to see what Row is being returned by variable iDataLastRow? Step thru the process using F8

 

[indyman]

Works for me. Both statements are returning a value of 3528.
Have you checked to see what Row is being returned by variable iDataLastRow? Step thru the process using F8

My iDataLastRow resolves to 639. I have found the issue. If I limit the SUMIFS to rows 3 through 5 (Where the above data comes from), I get the proper sum. But If I expand it beyond 199, it goes to zero. And upon further research, rows 204 thru 208 have negative amount totaling -3528. My conclusion is that the SUMIFS does not appear to be working for just the visible rows. I can confirm that with a cell formula too.

The new question is, does anyone know a way to use the ".SpecialCells(xlCellTypeVisible)" in the SUMIFS formula? Or another workaround?

 

[indyman]

After doing some more research, I can find no way to use SUMIFS on a filtered list. In fact I have found info that confirms that filtered data does nto support the SUMIF functions. I will just have to resort to doing the summation in a helper column.