Using Countif with a range

Microsoft Exchange server 2007 x64 dvd -...
April 6, 2011 at 13:31:01
Specs: Windows XP
(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.


See More: Using Countif with a range

Report •

#1
April 6, 2011 at 14:02:15
Didn't I already give you a formula for this?

There will be times when someone may input all three of the choices in a cell

So use a dropbox / data validation and don't allow them to enter anything but what you want.

MIKE

http://www.skeptic.com/


Report •

#2
April 6, 2011 at 14:24:23
Your formula did not work. You were unclear as to what I was asking for. I need to find an answer to this formula, not circumvent it or restrict it. The cell where this response goes is a "comment" cell, they may need to enter information that wouldn't be a normal response listed in a drop-down.

Report •

#3
April 6, 2011 at 14:51:40
Then separate it out and add a new column/cell for the specific purpose of entering a persons duty status.

It seems to me, that entering someones duty status in a comment field is counter productive, it makes trying to determine who is doing what that much more difficult, as you see.

I'm not trying to be difficult about this, but it makes no sense to me to try and cut apart a comment field and then count how many times specific words occur, in an attempt to get someones status, when having a dedicated field for that purpose would be simpler and far less prone to mistakes.


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 6, 2011 at 16:11:09
There's not enough room on the spreadsheet. I feel like I'm getting the run-around... I'll try another forum. There's got to be someone who knows the answer to this and not keep trying to get me to change forms I'm working with. If you don't know, it's okay... I'll find the answer.

Report •

Ask Question