Results 1 to 8 of 8

Thread: COUNTIF and AND

  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    COUNTIF and AND

    I want to find a formula that will give me a count of cells where C5:C44 contains "RN" and L5:L44 is not blank. (L5:L44 are formatted as General, but contain only text, or are empty).

    So far, no success after fooling with it for a while.

    Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Lou,

    Often, a SUMPRODUCT is easier to construct and understand when you are dealing with multiple criteria:

    =SUMPRODUCT((C5:C44="RN")*(L5:L44<>""))

    Naming the ranges makes the formula even easier to understand / debug.

    PS You can achieve what you want with COUNTIFS, but I still prefer SUMPRODUCT
    https://exceljet.net/formula/count-i...criteria-match
    Last edited by MartinM; 2016-09-28 at 12:46.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    Lou Sander (2016-09-28)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Lou

    ..if C5 = "ARNIE SCHWARZENEGGER" does that pass the 'contains "RN" test????

    zeddy

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    MartinM: I was aware of the SUMPRODUCT technique, but since I seldom use it, I forgot it. Thanks for the help!

    Zeddy: I don't think so. I put BURNS into C5, and it didn't get counted. I guess it is "equals" rather than "contains"
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Zeddy,

    Windows Secrets found an error in the sentence you have entered. Do you want to accept the correction proposed below?

    if C5 = "ARNIE SCHWARZENEGGER" does that pass the 'contains "RN"' test????

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    I have difficulty with apostrophe's ever since they started to drop them from London Underground Stations.
    There's only eight left now.

    zeddy
    (..and don't get me started on Newcastle's St. James' Park - what's that all about? In London, we go from King's Cross to St. James's Park underground)
    Last edited by zeddy; 2016-09-28 at 14:09.

  8. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2016-09-28)

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Lou,

    This will also work:

    Code:
    =COUNTIFS(C5:C44,"RN",L5:L44,"<>"&"")
    HTH,
    Maud

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2016-10-09)

  11. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Problem solved. Thanks to all!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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