No "blue"

[Accesscrzy]

I posted incorrectly. What I needed was a formula to count the following:
A1:A1000 is not blank + B1:B1000 is blank + C1:C1000 does not contain 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 blue
Red red

 

[Thomas [PBD]]

Accesscrzy,

This should help you.

=SUMPRODUCT(--(A1:A5<>"")*--(B1:B5="")*--ISERROR(SEARCH("blue",C1:C5)))

 

[Bernard Liengme]

Hello Thomas,
Please note that while
=SUMPRODUCT(--(A1:A5<>"")*--(B1:B5="")*--ISERROR(SEARCH("blue",C1:C5))) will
work, it is a bot inconsistant.
The purpose of the double negation is to coerse Boolean values (False/Ture)
into numeric (0/1) But the act of multiplication does this, so the double
negs are superfluous.

Either
=SUMPRODUCT((A1:A5<>"")*(B1:B5="")*ISERROR(SEARCH("blue",C1:C5)))
or
=SUMPRODUCT(--(A1:A5<>""), --(B1:B5=""), --ISERROR(SEARCH("blue",C1:C5)))


See: http://www.mcgimpsey.com/excel/variablerate.html
best wishes
Bernard Liengme (Excel MVP)

Accesscrzy,

This should help you.

=SUMPRODUCT(--(A1:A5<>"")*--(B1:B5="")*--ISERROR(SEARCH("blue",C1:C5)))

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''Yes'' below.

I posted incorrectly. What I needed was a formula to count the
following:
A1:A1000 is not blank + B1:B1000 is blank + C1:C1000 does not contain 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 blue
Red red