Help with Formula & Filters

[meggall]

Hey!

Wondering if I can get help. I'm using the below formula in a spreadsheet. I'm looking for it to update based on if I filter other parts of the table which would hide some of the rows between 2:999. Does anyone know how I can update my formula to ignore hidden rows?

=SUM((COUNTIF(W2:W999,"P")*$W1001)+(COUNTIF(W2:W999,"F")*$W$1002))

Thanks in advance!

 

[kweaver]

COUNTIF, unfortunately, cannot be used in the SUBTOTAL-type options.
There might be another simpler solution, but I did find one option.
It requires you to put all 1s in the X column, however.

=(W1001*SUMPRODUCT(SUBTOTAL(109,OFFSET(W2,ROW(W2:W999)-ROW(W2),1)),(W2:W999="P")+0))+$W1002*SUMPRODUCT(SUBTOTAL(109,OFFSET(W2,ROW(W2:W999)-ROW(W2),1)),(W2:W999="F")+0)

But I believe it'll work.

 

[meggall]

Thanks for the response!

I gave it a shot but came up with 0 so I might need some more tweaking to get that one working. I managed to get it working enough for the functions I need to filter for at the moment by assigning data to column U and manipulating cell W1003 as my "Filter"

=SUM((COUNTIFS(W2:W999,"P",$U$2:$U$999,$W$1003)*$W1001)+(COUNTIFS(W2:W999,"F",$U$2:$U$999,$W$1003)*$W$1002))