Advanced filter criteria

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows
I am trying to find the layout for Advanced Filter criteria as follows:

There are six classes in the school. Each day pupils record the date, their class (A,B,C,D,E,F), gender (boy / girl) and the fruit they ate that day (freetext, but including apple, pear, and banana) in columns in a worksheet.

I want to find out how many apples, pears and bananas classes A, B and C have been eating.

If I put A, B, C vertically in A1, A2 A3 and Apple, Pear, Banana in B1, B2, B3, this will interpret as Class A and Apple, Class B and Pear, Class C and Banana (ie excluding pears and bananas for A, Apples and Bananas for B, Apples and Pears for C). If I put Apple, Pear, Banana in B4, B5, B6 this will return all the other fruit eaten as well against all the other classes (D,E and F).

How do I set the criteria so that I get all Apple Pear and Banana for only classes A, B and C?

If I want to add the gender (boy/girl) so I can split it even further how do I do that?

Many thanks.

HT
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,384
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Doing it with straight values gets messy as you basically have to repeat every combination on a separate row. It's generally a lot easier to use formulas for the criteria. To do that, you need to use a criteria range header that doesn't match any of your actual table headers (you can just leave it blank) and then build a formula that refers to the first row of your data using relative references. So, for example, assuming your table is in columns A:D with headers in row 1 and first row of data in row 2, your formula might be something like:

Excel Formula:
=AND(OR(B2={"A","B","C"}),COUNT(SEARCH({"apple","pear","banana"},D2))>0,C2="boy')

When you use this as the criteria range, the filter will evaluate that formula for each row of the table starting with row 2, and only return rows where it evaluates to TRUE.
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows
Many thanks for your reply. I am afraid simplified the question in order to understand how it works in principle and make it simpler to explain. In fact I have many columns, one with 46 options. In order to make this user friendly I have created data validation dropdowns in the Criteria range based on the lists of options so all the user has to do is select what they want from the dropdowns.

If I want to have all the options for class and fruit type in my example as 'Or', should they all be in one column or how should they be laid out? I appreciate this may be complex and involve many rows but that's ok!

If I want to add 'date between' a start and end date based on the data entry dates, would this be on a separate row with the start and end dates in separate columns?

Many thanks for your help

HT
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,384
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You'll still want to use formulas, otherwise the number of possible rows you'd need to cover all combinations would be horrific. However, you can use more than one formula, so you could set up a different one for each column and just include them all in the criteria range. Then all you'd need for each column would be something like a MATCH formula testing if the first data cell for that column matches the list of selected criteria - eg =ISNUMBER(MATCH(D2,$Z$2:$Z$4,0)) would filter the data for those rows where column D matches one of the values in Z2:Z4 (which is where your data validation dropdowns would be).
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Many thanks but it doesn't quite work (although I can see how it should).

The lookup data is on a different worksheet and if I select the second row of data on that worksheet (which is different from the criteria in the dropdown at the head of the source list for the dropdown) it returns FALSE as you would expect. If I select the matching text (in D12) from the data column it returns TRUE (ie the data in the data column matches the data in the dropdown as it should) but it doesn't return the actual data so it can be used as criteria for the advanced filter.

I have played around with it a bit but I still can't get it to work. Should the range in the first part of the formula include the entire range of data ie D2:D500? I have tried this but it only returns TRUE as well!

Many thanks

HT
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,384
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What you’re describing is exactly what should happen. Did you try using those cells as the criteria range in the advanced filter?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
113
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sorry, being a bit dim here. The formula returns FALSE. If I use FALSE with the column header as extract range criteria it doesn't return anything.

Using only column D headed Fruit, D2 is Plum. Apple Pear and Banana are in many random cells further down column D. The criteria in the dropdowns are Z2: Apple, Z3 Pear, Z4 Banana. The dropdown range is Z9:Z20. The formula (result = FALSE) is in C3. C2 is Fruit so I have used C2:C3 as the Criteria Range for the filter. The List Range is A1:D200.

What am I doing wrong?

Many thanks for your help!

HT
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,384
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The criteria range header should NOT match any of the field names.
 

Forum statistics

Threads
1,141,873
Messages
5,709,104
Members
421,614
Latest member
RAB29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top