Apr 02 2021 07:56 AM
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
Apr 02 2021 08:02 AM
SolutionFiltering 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)
Apr 02 2021 08:09 AM
Apr 02 2021 08:30 AM
Apr 02 2021 08:31 AM
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.
Apr 02 2021 08:02 AM
SolutionFiltering 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)