How to use data validation and 'no filter' in excel-CodePudding

I'm currently trying to set up some filtering using data validation. One thing I can do is have the string "<>&" to mean any non-empty value i.e. to do no filtering.

This works when used in a countifs() formula. for example the number of brown dogs formula here is: =COUNTIFS(O10:O18,R5,P10:P18,R6) where R6 is currently 'dog' in blue but can be <>& this is working fine.

What I would like to do next is:

Count the number of unique owners for a given filter configuration, for example I would like to know how many unique owners have a brown dog?

There are 4 brown dogs, but two of those dogs are owned by the same owner ID (2), so the answer should be 3 i.e. the unique owner ID's matching the query are 2, 4, 5.

I tried various things with SUM(COUNTA(UNIQUE(FILTER())) but it seems the wild card for default or 'no filter' "<>&" does not work with the FILTER() function.

CodePudding user response：

Expand your Countifs to add "include" where=1 and you will have your count of 3.

CodePudding user response：

Managed to solved this in the end. effectively Filter() cannot understand "<>&" as a wildcard so instead we add an if statement to check for that, then to check if an item is in the list we do ISNUMBER(MATCH())

Here's the full expression for the cell below and the output:

=SUM(COUNTA(UNIQUE(FILTER(L10:L18, (IF($R$6="<>&", TRUE, ISNUMBER(MATCH(P10:P18,$R$6,0))))*(IF($R$5="<>&", TRUE, ISNUMBER(MATCH(O10:O18,$R$5,0))))))))