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, 2022 in Others by Kithuzzz
• 38,010 points
379 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, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
511 views
0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
374 views
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, 2022 in Others by narikkadan
• 63,700 points
656 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,740 points
960 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,090 points
875 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,090 points
4,150 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
3,916 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, 2022 in Others by narikkadan
• 63,700 points
2,239 views
0 votes
1 answer
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