Count entries in a range that match a certain criteria within data  
Author Message
pseudomoney





PostPosted: Tue Feb 05 22:20:46 CST 2008 Top

Excel Misc >> Count entries in a range that match a certain criteria within data

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

Excel92  
 
 
T





PostPosted: Tue Feb 05 22:20:46 CST 2008 Top

Excel Misc >> Count entries in a range that match a certain criteria within data Try this:

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

--
Biff
Microsoft Excel MVP




> 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





PostPosted: Tue Feb 05 22:23:22 CST 2008 Top

Excel Misc >> Count entries in a range that match a certain criteria within data =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


>
> 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
 
 
HammerJoe





PostPosted: Tue Feb 05 22:35:11 CST 2008 Top

Excel Misc >> Count entries in a range that match a certain criteria within data 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.