count all cells containing "Blue"

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
 
T

T. Valko

Try it like this:

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

Accesscrzy

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!!!
 
B

Bob Umlas

=SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISERROR(SEARCH("blue",C1:C1000))))
Bob Umlas
Excel MVP
 
A

Accesscrzy

Awesome... once again! Thank you! I still don't know how you guys (and
ladies) do it. Thank you so much!
 
D

Don Guillett

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
sumproduct() function resulted in an error #DIV/0 2
Unique Value Count in 3 Columns Using Search Criteria 0
conditional count of single occurences 4
Shift down and then to the right 1
Sumproduct - meet multiple criteria from a list 6
Using "--" as an array formula shortcut / alternative 9
count if (possibly?!) 6

Top