Solved How do you use Countif as an either / or?

Microsoft Exchange server 2007 x64 dvd -...
March 28, 2011 at 10:24:56
Specs: Windows XP
If I use the formula below and all three are listed in the cell, the count equals 3.


How can I get it to count only one of the three? All three mean the same thing, but sometimes people will type in more than one in the comment. e.g. "oji - on light duty"

I'm trying to combine an "or" with the countif:


Is the syntax wrong? or can you not use the "or" with countif? or is there a better way?

See More: How do you use Countif as an either / or?

Report •

March 28, 2011 at 17:36:30
✔ Best Answer
I've been following this saga, and I've gotten none of your formulas to get out of error mode.

Try this:

=IF(COUNTIF(A5, "*e*")+COUNTIF(A5, "*a*")+COUNTIF(A5, "*o*")>0,"TRUE","")

Report •

March 28, 2011 at 17:52:51
Paul1149, like yours a lot better then mine.
I keep forgetting bout the wild cards.


Report •

March 28, 2011 at 18:09:34
Well then, maybe we're even now. You whipped my butt the other day on the MID function: Pure poetry! :)

Yeah, that countif function indeed is cool.

Report •

Related Solutions

April 3, 2011 at 12:59:27
Yep, MY screw-up... my old eyes are messing with me. The parenthesis were supposed to be braces around the choices. I'm sorry for the frustration.


I tried your formula and the outcome is "TRUE" but it doesn't count how many. If I
change "TRUE" to counta(j34), then it counts as "1".


However, what I didn't tell you was that I am counting the occurences in a RANGE of cells (J34:J46), but J34 has more than one word contained in the cell and right now my formula counts each of them, I need it to count only one per cell within the range. I didn't mention this before because I figured if you were able to give me the formula for ONE cell, it would work for a range. I guess I should have filled you in all the way.

When I use the following formula I get "1":


If I change it to a range I still get "1":

Does excel's formulas allow you in some way to incorporate an "OR" after "J34:L46," with a particular syntax? The following doesn't work.


Report •

April 3, 2011 at 13:10:51
All along you stressed that you wanted to count only one hit in a given cell. Now you say you wanted the actual count? I find it very hard to follow your requests.

If you want what you originally said you want, this will give you a numeric value to tabulate:

=IF(COUNTIF(A5, "*e*")+COUNTIF(A5, "*a*")+COUNTIF(A5, "*o*")>0,1,"")

If you want further help regarding a range I suggest you put the question into more succinct terms.

Report •

April 3, 2011 at 13:14:36
Major mistake made. I apologize.

Report •

April 3, 2011 at 13:30:09
I looked back at what I wrote, and although I thought I was expressing what I wanted, I understand now how you took it. However, it you read the explanation below, and then reread my original request, you will see that it reads that way also. I apologize for not seeing how it could be mistaken.

The formula must count the number of times each cell in the range contains either of the choices. If a cell within the range contains one of the three, the count of that cell = "1". If a cell within the range contains three of the three choices, the count for that cell is still = "1".

Report •

October 3, 2011 at 05:25:27
I have a query along the same lines as this:

I am using a formula: =COUNTIF('FRICE-RAW-DATA'!AC31:AC34, 25%)+COUNTIF('FRICE-RAW-DATA'!AC31:AC34, 50%)+COUNTIF('FRICE-RAW-DATA'!AC31:AC34, 75%)+COUNTIF('FRICE-RAW-DATA'!AC31:AC34, 100%)

It looks a mess due to linking to another worksheet (FRICE-RAW-DATA), what it is basically saying is that if the value 25% appears in cells AC31:AC34, count 1. So if it appears 4 times the count will be 4 etc.

It then does this with any values 25, 50, 75, or 100% to count 1. So if in 31-34 we have 25, 25, 50, 25 the count should simply be 4.

Am I on the right track?

Report •

Ask Question