Excel Magic Trick 683: 3 AND criteria & 3rd AND has OR criteria -- Add, Count & Conditional Format

For Data Analysis learn about AND & OR criteria and Boolean and see how to:
1) Counting with 3 AND criteria and the 3rd AND criteria contains an OR criteria
2) Adding with 3 AND criteria and the 3rd AND criteria contains an OR criteria
3) Conditional Formatting with 3 AND criteria and the 3rd AND criteria contains an OR criteria

Download file: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• AND
• criteria
• 3rd
• has
• OR
• criteria:
• Add
• Count
• Conditional
• Format
• Mike
• Gel
• excelisfun
• Girvin
• Highline
• Community
• College

[emailuznow]

Hi Mike

Can we do the same construct above (i.e AND, AND & OR) Criteria in anvanced filter?

[ExcelIsFun]

Yes:

AND criteria goes on the same line (row), even if you have to repeat field names

OR criteria goes on separate lines (rows)

See this video:

Highline Excel Class 19: Advanced Filter Extract Data 9 Examples

The Key is that if you have three AND and third AND is an OR, list the first two AND's on two lines (rows) and the third one should have the first OR one line 1 (row 1) and the second OR is on line 2 (row 2). The video has an example just like your question.

[fishmanboy]

Great stuff... Really interesting and well presented. Keep em coming :-)

[ExcelIsFun]

You got it!

I already have the next 350 videos planned out (plus another 100 random ones that will pop up in-between the planned ones). It just takes so long to produce and post each video that I can only get to about 5 - 10 per week...

[iamipula]

maybe others asked this but i'm interested... how do you record your excel window?

[ExcelIsFun]

A Progarm called Camtasia.

[ExcelIsFun]

You are welcome!!

[6642633]

Thanks being v usefulll