Excel SUMPRODUCT question

[yanivp1]

Need help please:


At cell P177 this function apears:
=SUMPRODUCT(SUBTOTAL(2,OFFSET(P8,ROW(P8:P162)-ROW(P8),0,1)),--(P8:P162=1))


At this situation the function summarize the total appearances of number 1 between cells P8 to P162.


Now I want to update the function, that it will summarize the total appearances of number 1 or 2 between cells P8 to P162.


Thanks a lot for your help.
Yaniv.

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(P8,ROW(P8:P162)-ROW(P8),0,1)),(P8:P162=1)+(P8:P162=2))

However, the following is more efficient...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(P8,ROW(P8:P162)-ROW(P8),0,1)),--ISNUMBER(MATCH(P8:P162,{1,2},0)))

Hope this helps!

 

[yanivp1]

Try...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(P8,ROW(P8:P162)-ROW(P8),0,1)),(P8:P162=1)+(P8:P162=2))

However, the following is more efficient...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(P8,ROW(P8:P162)-ROW(P8),0,1)),--ISNUMBER(MATCH(P8:P162,{1,2},0)))

Hope this helps!

Thanks a lot, friend. It helped!