# Thread: COUNTIF and AND

1. ## 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?

2. 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

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

Lou Sander (2016-09-28)

4. Hi Lou

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

zeddy

5. 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"

6. 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. 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)

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

MartinM (2016-09-28)

9. 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. Problem solved. Thanks to all!

#### Posting Permissions

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