Forum Discussion
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 is;-
=COUNTIFS(Table1[Leverage],"Draw",Table1[Result70],"Draw",Table1[75-90Yes],1)
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
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)
4 Replies
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.
