Count entries in a range that match a certain criteria within data |
|
Author |
Message |
pseudomoney
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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.
|
|
|
|
|
|
|