Help with COUNTIFS using Filtered Results

New Contributor


I'm trying to use COUNTIFS with filtered results probably need to use SUMPRODUCT SUBTOTAL & OFFSET - but just cant get my head around the Syntax.


An example of the  COUNTIFS that I am using is;-


This formula works fine but obviously if I filter the table the results do not change.

Any help would be much appreciated.

FYI - I'm using Office Pro Plus 2019

4 Replies
best response confirmed by keitra (New Contributor)


Filtering doesn't affect COUNTIFS(). As variant you may add helper column to the table like


and modify your formula to count only non-filtered rows



Thanks for the reply, but I'm not sure I understand. I was expecting something like ;-
Are you saying I cannot use COUNTIFS & get results from only visible rows/cells ?
I tried your suggestion and it works fine thank you. Seems a messy solution though.
Again thanks for your help Sergei


Only two functions, SUBTOTAL() and AGGREGATE() could ignore hided rows. SUBTOTAL with OFFSET is another possible variant, just I personally prefer AGGREGATE(), easier in maintenance.