Hi,

On cells D1:D50, each cell has a list of several options to choose.
(Used Data-Validation-Allow-List, Source="Apple, Orange,Grape,Nop-
Apple,Nop-Orange,Nop-Grape")

So on each cell I can choose of the options available in the source
field.
I need to be able to count all the cells in the range D1:D50 that
contain the first three letters "Nop"

I tried Sumproduct function but it either gives zero or a #value error
and countif if using it like this : =COUNTIF(D1:D5, "=Nop-Apple"),
but I want something like this =COUNTIF(D1:D50, LEFT(D1:D50,3)="Nop"))
which of course doesnt work
Can someone help with this?

Thanks

Try this:

=COUNTIF(D1:D50, "Nop*")

--
Biff
Microsoft Excel MVP


wrote in message
...
Hi,

On cells D1:D50, each cell has a list of several options to choose.
(Used Data-Validation-Allow-List, Source="Apple, Orange,Grape,Nop-
Apple,Nop-Orange,Nop-Grape")

So on each cell I can choose of the options available in the source
field.
I need to be able to count all the cells in the range D1:D50 that
contain the first three letters "Nop"

I tried Sumproduct function but it either gives zero or a #value error
and countif if using it like this : =COUNTIF(D1:D5, "=Nop-Apple"),
but I want something like this =COUNTIF(D1:D50, LEFT(D1:D50,3)="Nop"))
which of course doesnt work
Can someone help with this?

Thanks

=countif(d1:d50,"nop*")

if you wanted to use =sumproduct() (don't do this!)
=sumproduct(--(left(d1:d50,3)="nop"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

" wrote:

Hi,

On cells D1:D50, each cell has a list of several options to choose.
(Used Data-Validation-Allow-List, Source="Apple, Orange,Grape,Nop-
Apple,Nop-Orange,Nop-Grape")

So on each cell I can choose of the options available in the source
field.
I need to be able to count all the cells in the range D1:D50 that
contain the first three letters "Nop"

I tried Sumproduct function but it either gives zero or a #value error
and countif if using it like this : =COUNTIF(D1:D5, "=Nop-Apple"),
but I want something like this =COUNTIF(D1:D50, LEFT(D1:D50,3)="Nop"))
which of course doesnt work
Can someone help with this?

Thanks

--

Dave Peterson

Thanks for the help.

I got it working with SUMPRODUCT((D1:D50<"")*(LEFT(D1:D50,3)="Nop"))
I swear that I tried it before and it didnt work, but now it does... I
said it before, will say it again, Sumproduct frustrates me to no end.

But I like the =COUNTIF(D1:D50, "Nop*") better, it is simpler and
easier to work with. Thanks Valko.

Dave, your help is as usual appreciated and very instructive. I like
sumproduct alot but it takes me awhile to get it working. Go figure.