count all cells containing "Blue"

  • Thread starter Thread starter Accesscrzy
  • Start date Start date
A

Accesscrzy

I need a formula to count the following:
A1:A1000 is not blank + B1:B1000 is blank + C1:C1000 contains the word blue.
=Sumproduct((A1:A1000<>â€â€)*( B1:B1000=â€â€)*( C1:C1000=â€*blueâ€))??
Answer: 2

Col A Col B Col C
Red Lt Blue
Blue
Tan red
Pink Dark Blue

Thx
 
Try it like this:

=SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISNUMBER(SEARCH("blue",C1:C1000))))
 
That worked great!
Except... I gave you the wrong instructions! Soooo Sorry.
The part of the formula that says... contains "blue"... I do NOT want it to
contain "blue".
My bat!!!
 
=SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISERROR(SEARCH("blue",C1:C1000))))
Bob Umlas
Excel MVP
 
Awesome... once again! Thank you! I still don't know how you guys (and
ladies) do it. Thank you so much!
 
As always, Bob is right on. One small addition in case someone touched the
space bar in col B
SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISERROR(SEARCH("blue",C1:C1000))))

SUMPRODUCT(--(A1:A1000<>""),--(trim(B1:B1000)=""),--(ISERROR(SEARCH("blue",C1:C1000))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No "blue" 2
Problem calculating 1
Problem calculating 3
count if (possibly?!) 6
IF AND OR STATEMENT 3
Using "--" as an array formula shortcut / alternative 9
somewhat complicated conditional formula 2
Count blanks by date 2

Back
Top