|
|
|
date: Fri, 3 Oct 2008 11:13:00 -0700,
group: microsoft.public.excel
back
Re: count all cells containing "Blue"
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))))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Accesscrzy" wrote in message
news:E3A8B0A1-ECC8-417E-AA24-55906DB15417@microsoft.com...
> Awesome... once again! Thank you! I still don't know how you guys (and
> ladies) do it. Thank you so much!
>
> "Bob Umlas" wrote:
>
>> =SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISERROR(SEARCH("blue",C1:C1000))))
>> Bob Umlas
>> Excel MVP
>>
>> "Accesscrzy" wrote in message
>> news:CEC4E2D5-2E9C-402A-86BB-1B2A26086D5A@microsoft.com...
>> > 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!!!
>> >
>> > "T. Valko" wrote:
>> >
>> >> Try it like this:
>> >>
>> >> =SUMPRODUCT(--(A1:A1000<>""),--(B1:B1000=""),--(ISNUMBER(SEARCH("blue",C1:C1000))))
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Accesscrzy" wrote in message
>> >> news:5BAB44D6-0115-4E85-8906-32CD1D794DCD@microsoft.com...
>> >> >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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
date: Sat, 4 Oct 2008 08:17:37 -0500
author: Don Guillett
|
|
