Countifs with an OR function

January 9, 2012 at 18:44:59
Specs: Windows 7
I have tried searching for this but cannot seem to find the answer, so if it is covered already I apologise.

I have some data that I am using the COUNTIF function for but want Excel to count if it matches two or more criteria.

For example (assuming the first item is in cell A1) I have the below values in a sheet


If I wanted to know how many time Orange was listed I would use =countif(A:A,"Orange")

How do I count 2 or more different criteria in the one argument without the need to count them seperately. For example if I wanted to know how many Apples and Orages were in the list

I could use: =countif(A:A,"Orange")+countif(A:A,"Apple")
Is there any way I could do this usinsg something like =countif(A:A,or("Apple","Orange"))

The reason I ask is my data is actually validating against up to 5 different ranges for the COUNTIF and some of these ranges I would like it to count if

Range A contains x OR y OR z
AND Range B contains q OR w OR e
AND Range C contains b

To have Excel count for each possibility makes for a very long and cumbersome formula.

Before you ask I have tried COUNTIFS but still run into the same issues when trying to count if the range contains this OR that...

Thanks in advance for your help!!!

See More: Countifs with an OR function

January 9, 2012 at 19:48:16

Report •
Related Solutions

Ask Question