Forum Discussion
Solved
Help with COUNTIFS using Filtered Results
Hi, 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 ...
Filtering doesn't affect COUNTIFS(). As variant you may add helper column to the table like
=AGGREGATE(3,5,[@Leverage])and modify your formula to count only non-filtered rows
=COUNTIFS(Table1[Leverage],"Draw",Table1[Result70],"Draw",Table1[75-90Yes],1,Table1[Helper],1)
Filtering doesn't affect COUNTIFS(). As variant you may add helper column to the table like
=AGGREGATE(3,5,[@Leverage])and modify your formula to count only non-filtered rows
=COUNTIFS(Table1[Leverage],"Draw",Table1[Result70],"Draw",Table1[75-90Yes],1,Table1[Helper],1)
- Thanks for the reply, but I'm not sure I understand. I was expecting something like ;-
=SUMPRODUCT(SUBTOTAL(3,OFFSET([30-45],ROW([30-45])-MIN(ROW([30-45])),,1)),--([30-45]>1))
Are you saying I cannot use COUNTIFS & get results from only visible rows/cells ?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.
- I tried your suggestion and it works fine thank you. Seems a messy solution though.
Again thanks for your help Sergei
