Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Advance thanks to all.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    =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. #3
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    al is et's first cousin twice removed...
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Brother of "Bottles" I suppose

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    In American English that would translate into Bubba!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Think Capone !

  9. #9
    Lounger
    Join Date
    Jan 2013
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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
  •