How to use data validation and no filter in excel

0 votes

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?

Nov 3 in Others by Kithuzzz
• 20,660 points
41 views

1 answer to this question.

0 votes

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

enter image description here

I hope this helps you. 

answered Nov 4 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
75 views
0 votes
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
138 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
397 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,373 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,837 views
0 votes
1 answer

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
108 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP