How to use data validation and no filter in excel

Question

I'm currently attempting to set up some data validation-based filtering. One thing I can do is make the string ">&" stand for any non-empty value, effectively removing filtering.

When incorporated into a countifs() calculation, this works. For instance, the calculation for the number of brown dogs in this example is =COUNTIFS(O10:O18,R5,P10:P18,R6), where R6 is now displayed as "dog" in blue but may be changed to >& this is operating as intended.

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?

Answer 1

eventually succeeded in resolving this. Effectively, Filter() cannot recognize">&" as a wildcard, so we add an if statement to check for that. Next, we use ISNUMBER(MATCH()) to determine whether an item is included in the list.

Here is the complete phrase for the cell below, where you can insert a picture description to see the result:

=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))))))))

I hope this helps you.