COUNTIF - one of three choices

[sctlippert]

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 like an "or" function, but is like an "and" function, ending up with the count of "3" if all three choices are listed in the cell:

=SUM(COUNTIF(1C:3C,{"*one*","*two*","*three*"}))

cell 1C = one, two, and three
cell 2C = two and three

cell 3C = three

The formula above will return a value of "6", I need it to function as an "or" function and count the above cell occurrences as "3", "1" for each cell.

[Cutter]

Duplicate post:

http://www.excelforum.com/windows-xp...e-choices.html