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, USA
    Posts
    902
    Thanks
    153
    Thanked 11 Times in 10 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
    1,080
    Thanks
    72
    Thanked 122 Times in 107 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 11: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
    3,225
    Thanks
    154
    Thanked 608 Times in 578 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, USA
    Posts
    902
    Thanks
    153
    Thanked 11 Times in 10 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
    1,080
    Thanks
    72
    Thanked 122 Times in 107 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
    3,225
    Thanks
    154
    Thanked 608 Times in 578 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 13: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,839
    Thanks
    142
    Thanked 723 Times in 657 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, USA
    Posts
    902
    Thanks
    153
    Thanked 11 Times in 10 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
  •