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?

Report •


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

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

MIKE

http://www.skeptic.com/


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 I
change "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
Major mistake made. I apologize.
.
.
.
.
.
.

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 •


Ask Question