# 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. =sum(countif(J34,("*oji*","*duty*","*light*")))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:=sum(COUNTIF(or(J34,"*oji*"),(J34,"*duty*"),(J34,"*light*")))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?

#1
March 28, 2011 at 17:36:30
 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 •

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

Report •

#3
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: http://www.computing.net/answers/of... Pure poetry! :)Yeah, that countif function indeed is cool.

Report •

Related Solutions

#4
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.=SUM(COUNTIF(J34,{"*OJI*","*light*","*duty*"}))I tried your formula and the outcome is "TRUE" but it doesn't count how many. If Ichange "TRUE" to counta(j34), then it counts as "1". =IF(COUNTIF(J34,"*OJI*")+COUNTIF(J34,"*light*")+COUNTIF(J34,"*duty*")>0,counta(j34),"")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":=SUM(OR(COUNTIF(J34,"*oji*"),COUNTIF(J34,"*duty*"),COUNTIF(J34,"*light*")))If I change it to a range I still get "1":=SUM(OR(COUNTIF(J34:L46,"*oji*"),COUNTIF(J34:L46,"*duty*"),COUNTIF(J34:L46,"*light*")))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.countif(J34:L46,or("*a*","*b*","*c*"))

Report •

#5
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 •

#6
April 3, 2011 at 13:14:36

Report •

#7
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 •

#8
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 •