Microsoft Exchange server 2007 x64 dvd -...

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?

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

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.

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.

The SUM was missing from your first post.

That's what I just said.

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 matchMIKE

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History