Count entries in a range that match a certain criteria within datavalidation.

[HammerJoe]

Hi,

On cells D150, 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 D150 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(D15, "=Nop-Apple"),
but I want something like this =COUNTIF(D150, LEFT(D150,3)="Nop"))
which of course doesnt work
Can someone help with this?

Thanks

 

[T. Valko]

Try this:

=COUNTIF(D150, "Nop*")

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[HammerJoe]

Thanks for the help.

I got it working with SUMPRODUCT((D150<>"")*(LEFT(D150,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(D150, "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.