Subtotal IF

[DinaMae]

Hi, this might sound weird..but I'm doing the reverse engineering way.

I got this formula :
= SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),(C2:C6="Y")+0)

and applied to my requirement:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1)),(H4:H773="P")+0)

which perfectly worked on what I am looking for.... but I want to understand how the formula really works, what is the logic behind. So it would be easy for me to apply it when a similar scenario comes out.

Thanks.

 

[pgc01]

Hi
Welcome to the board

Your formula is like

=SUMPRODUCT(M4:M773,(H4:H773="P")+0)

that adds the values in column M for the values in column H equal to "P".

The difference is that you want to apply a filter to the range and ignore the rows that that are hidden by the filter.



OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1) generates an array with the individual references for each cell in the range: (M4,M5, ... ,M773)

SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1))

generates an array with a value for each of the cells:
- If the cell is visible, SubTotal() function returns the value of the cell.
- If the cell is not visible, because a filter was applied, the SubTotal() function returns 0.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1)),(H4:H773="P")+0)

adds the values in column M, ignoring the ones that hidden due to a filter, when the cells in column H in the same row have the value "P".

Does this help?

 

[DinaMae]

Ok I got it PGC! thanks alot.. =)

 

[pgc01]

You're welcome. Thanks for the feedback.