(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.
Hi,
Here's one way:
=SUMPRODUCT(--(ISNUMBER(SEARCH("oji",C1:C4))+ISNUMBER(SEARCH("light",C1:C4))+ISNUMBER(SEARCH("duty",C1:C4))>0))
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
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks