# Thread: Count Cells Containing "IMMEDIATE"

1. ## Count Cells Containing "IMMEDIATE"

Hi, I have a spreadsheet that contains several concantenated words in column AO range AO2 to AO68470. I would like to count the cells that have the word "IMMEDIATE" in them. A simple Countif will not work because a cell that contains "IMMEDIATE" may also have "HOLD" in the same cell and read "IMMEDIATEHOLD".

Any ideas on how to do this?

2. =COUNTIF(A02:A068470,"*IMMEDIATE*") will do it.

Notes:

1. This formula will only count one instance even if the word IMMEDIATE occurs twice in a cell.

2. This formula is NOT case sensitive, so it will count IMMEDIATE, ImMeDiAtE and immediate, and any other combination of upper and lower case characters.

3. Martin, et al, this works great. Now, if in that same range I want to count cells that contain "Hold" but do not contain "Immediate" then how to I count those?

Thanks again.

4. To simplify things I have given your Range AO2 to AO68470 a name . . . Inputs.

This formula will give you the answer you want:

=SUMPRODUCT(NOT(ISERROR(SEARCH("hold",Inputs)))*ISERROR(SEARCH("immediate",Inputs)))

Notes:

1. Sorry about all the brackets !
2. I used the SEARCH function because, unlike FIND, it is not case-sensitive.
3. When SEARCH cannot find the string it returns the error #VALUE!, so that is the test you are looking for.
4. The part in RED tests for the presence of hold in the string.
5. The part in BLUE tests for the absence of immediate in the string.
6. The SUMPRODUCT function is ideal for testing multiple criteria - you can have up to 30 such tests in one statement.
7. PS: Who is al ?

5. Martin,

al is et's first cousin twice removed...

6. Brother of "Bottles" I suppose

7. In American English that would translate into Bubba!

8. Think Capone !

9. Now another thing to count. I want to count, in the same range, ao2:ao64860 cells that contain:
"0" OR "No Call Made" OR "#N/A"

but that do NOT contain "Immediate" OR do NOT contain "Hold" OR do NOT contain "BWH" OR do NOT contain "HR"

How to do?

10. You just have to add more conditions to the SUMPRODUCT function, whose general form is

SUMPRODUCT( (Condition1) * (Condition2) * (Condition3) etc )

If ALL the conditions are TRUE, the result of the function is 1 but, if ANY of them is FALSE, the result is 0.

It would be worth experimenting with this function - do post back if you find it too hard.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•