SOLVED

Help with COUNTIFS using Filtered Results

%3CLINGO-SUB%20id%3D%22lingo-sub-2250960%22%20slang%3D%22en-US%22%3EHelp%20with%20COUNTIFS%20using%20Filtered%20Results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2250960%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20COUNTIFS%20with%20filtered%20results%20probably%20need%20to%20use%20SUMPRODUCT%20SUBTOTAL%20%26amp%3B%20OFFSET%20-%20but%20just%20cant%20get%20my%20head%20around%20the%20Syntax.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20of%20the%26nbsp%3B%20COUNTIFS%20that%20I%20am%20using%20is%3B-%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Table1%5BLeverage%5D%2C%22Draw%22%2CTable1%5BResult70%5D%2C%22Draw%22%2CTable1%5B75-90Yes%5D%2C1)%3C%2FP%3E%3CP%3EThis%20formula%20works%20fine%20but%20obviously%20if%20I%20filter%20the%20table%20the%20results%20do%20not%20change.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3EFYI%20-%20I'm%20using%20Office%20Pro%20Plus%202019%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2250960%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

4 Replies
best response confirmed by keitra (New Contributor)
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)

 

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 ?
I tried your suggestion and it works fine thank you. Seems a messy solution though.
Again thanks for your help Sergei

@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.