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.
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.