use countif, but criteria as this or that

Microsoft Exchange server 2007 x64 dvd -...
March 25, 2011 at 16:27:29
Specs: Windows XP
Using Excel 2003:

If I use the formula below and all three are listed in the cell, the count equals 3.

=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:

=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: use countif, but criteria as this or that

Report •

#1
March 26, 2011 at 14:08:05
If I use the formula below ......

=countif(J34,("*oji*","*duty*","*light*"))

First, explain how you can use the formula, when all I get are error messages?

and all three are listed in the cell, the count equals 3.

All three text strings are in a single cell?

MIKE

http://www.skeptic.com/


Report •

#2
March 26, 2011 at 19:01:21
I am not at the office and won't be back until Monday. I tried this formula at home and get error messages also... I wrote it from memory instead of copying it. I apparently wrote it down wrong. Will retry post on Monday.

Report •

#3
March 28, 2011 at 03:56:27
I did not list the formula correctly, the formula I have currently is:

=sum(countif(J34,("*oji*","*duty*","*light*"))

This counts EVERY occurence within a cell. I need to count only one of the three.


Report •

Related Solutions

#4
March 28, 2011 at 09:55:31
The SUM was missing from your first post.

Report •

#5
March 28, 2011 at 10:20:43
That's what I just said.

Report •

#6
March 28, 2011 at 14:34:20
This formula will get you what you want, but if I may ask, why are you letting them enter data, that you will need to check, into a free form comment field?

Do a dropbox and force them choose from a list of inputs,
that way you have control and will not have to use this very long and convoluted formula.

First

Assuming your comment field is cell A1

You must enter your three word to be checked into cells D1, D2, D3

Enter the formula:

=IF($D1=0," ",IF(ISERROR(FIND(LOWER($D1),LOWER(A1),1)),IF($D2=0," ",IF(ISERROR(FIND(LOWER($D2),LOWER(A1),1)),IF($D3=0," ",IF(ISERROR(FIND(LOWER($D3),LOWER(A1),1)),0,1)),1)),1))

It will return the number one if there is a match
It will return the number zero if there is no match

MIKE

http://www.skeptic.com/


Report •

Ask Question