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))))
 
Back
Top