Searching rows with multiple columns criteria

[Guest]

How do I find all rows in a range that contain a particular value in one
column and another value in a different column? e.g. Find all rows where
column B = "apple" and column C = "pie".

 

[Bernard Liengme]

What do you mean by 'find' ?
To count them use =SUMPRODUCT(--(B1:B100="apple"),--(C1:C100="pie"))
To display them in some color use Format Conditional Formatting with Formula
IS
=AND(B1="apple", C1="pie")
best wishes - please come back is more is needed

 

[Guest]

Use an Auto Filter on the top line of your spreadsheet, where all your
headers sit. (under Data - Filter - Auto Filter). Then using the drop down
box of the data filter for column B, select "(custom)". Select Equals as you
where condition and in the value area type "apple". Select OK and this should
have narrowed down the displayed data in column B to those row containing
"apple".

Do the same for column C, i.e. use the drop down box of the data filter for
column C, select "(custom)". Select Equals as you where condition and in the
value area type "pie". Select OK and this should have narrowed down the
displayed data in column C to those row containing "pie".

Now you should only be looking at rows that have apple in column B and pie
in column C.

 

[Guest]

This is great, Bernard! As I evaluated the formula, I could not tell what
the two "--"s were accomplishing. Nor could I find "--" in the help. What
does "--" accomplish?

Thanks,
William

 

[Bernard Liengme]

Glad it worked.
For SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and this explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html