Microsoft Exchange server 2007 x64 dvd -...

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?

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","")

Paul1149, like yours a lot better then mine.

I keep forgetting bout the wild cards.MIKE

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.

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*"))

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.

Major mistake made. I apologize.

.

.

.

.

.

.

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".

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?

Ask Your Question

Weekly Poll