COUNTIF one of three choices

(Excel 2003) Using countif with a range, the formula must count the number of times each cell in the range contains either of three choices. There will be times when someone may input all three of the choices in a cell, and I need it to count only "one" of the three. If a cell within the range contains one of the three, the count of that cell = "1". If a cell within the range contains all three of the three choices, the count for that cell is still = "1". The following countif statement is the best I have come up with so far, but unfortunately it doesn't count only one of the three, but ends up with the count of "3" if all three choices are listed in the cell:

=SUM(COUNTIF(1C:3C,{"*OJI*","*light*","*duty*"}))

cell 1C = OJI, on light duty
cell 2C = light duty
cell 3C = OJI

The formula above will return a value of "6", I need it to count "3", "1" for each cell.

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

As you are quite new, I will do it for you this time. Please have a look at forum rules