Help with COUNTIFS using Filtered Results

[keitra]

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

[#2 · Accepted solution]

@keitra 

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)

[#3]

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 ?

[#4]

I tried your suggestion and it works fine thank you. Seems a messy solution though.
Again thanks for your help Sergei

[#5]

@keitra 

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.