proper formula for countif (a1:b10, "apple && pie")

[alex]

Could I get some assistance with a criteria for (i think, COUNTIF)?
Using the below as an example.

what would be formula be to say:

=number of rows that have "apple" and "pie" (2)

A B C

1 apple pie MaryJane
2 apple muffin Sue
3 apple pie BettyBoop
4 peach pie Lilly
5 peach muffin Cate

or is this to compliated? none of the examples that I've found have
this specifically.

thanks,
Alex

 

[Andy Wiggins]

Assume your data is in the range A1:C5 then

=SUMPRODUCT((A1:A5="apple")*(B1:B5="pie"))

will do the trick

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[alex]

thanks for that...very ingenious
(I had to get the figure out what you were doing there
-alex