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

Apple

Orange

Grape

Orange

Apple

Apple

Orange

Grape

Cherry

Pumpkin

OrangeIf 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 bTo 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!!!

Have you looked into SUMPRODUCT?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll