Count by criteria

  • Thread starter Thread starter nrage21
  • Start date Start date
N

nrage21

I have 3 columns and 10 rows... the list will continue to grow o
course. I need a function that will count cells in column C wit
instances of 'names' for example..

A.............B...............C
Date....Completed...Name
Mar 20....Yes...........Jose
Mar 21....No............Maria
Mar 22....Yes...........Jose
Mar 23.....No...........Jose

and so on...
so the formula could look like... =DCountA(A1:A5,"Name","Jose")
but of course that function is not working... result should be 3
because there are three instances of "Jose" in column C for the entir
range.

Can some1 help?

- Larry -
VBA Amateu
 
Hi

try

=Countif(C1:C5,"Jose")

if you want the range (C1:C5) to be automatically redefined when you add
additional rows you're best option (IMHO) is to define a dynamic range
name - check out Debra Dalgleish's site for details on how to do this:
http://www.contextures.com/xlNames01.html#Dynamic

Hope this helps - let us know how you go.

Cheers
JulieD
 
If I read you correctly, you want a formula that will
return the number of times a given value appears in the
column. This should do what you need:

=Countif(C1:C100,"Jose")

I hope that's what you were looking for.

tod
 
OOPs sorry guys! read below to read what I ment to say

A.............B...............C
Date....Completed...Name
Mar 20....Yes...........Jose
Mar 21....No............Maria
Mar 22....Yes...........Jose
Mar 23.....No...........Jose
Mar 20....Yes..........Jose

I want the function to count the instances "jose" appears with th
"Date" as criteria. The function should return for Mar 20, tw
instances of "Jose".

Sorry for the messing up!

- Larry -
VBA Amateu
 
Thanks Frank! it works beautifully!

Here's the final function... linked to workbook1.

=SUMPRODUCT(('[Workbook1.xls]Sheet1'!$A$1:$A$10=DATE(2004,3,20))*('[Workbook1.xls]Sheet1'!$C$1:$C$10="Jose"))

- Larry
 
A new condition...

Sheet1, column L has the text "Completed"

How will I include this new condition into the worksheet function??

I want the function to count the instances "jose" appears in the lis
and to use "Date" and "Completed" as criteria.

This doesn't seen to work... can some1 show me the right way... :(

=SUMPRODUCT((Sheet1!$A$2:$A$912=DATE(2004,3,20))*(Sheet1!$C$2:$C$912="jose"),(Sheet1!$L$2:$L$912="Completed"))


- Larry -
VBA Amateu
 
Hi
try
=SUMPRODUCT((Sheet1!$A$2:$A$912=DATE(2004,3,20))*(Sheet1!$C$2:$C$912="j
ose")*(Sheet1!$L$2:$L$912="Completed"))

or
=SUMPRODUCT(--(Sheet1!$A$2:$A$912=DATE(2004,3,20)),--(Sheet1!$C$2:$C$91
2="jose"),--(Sheet1!$L$2:$L$912="Completed"))
 
Thanks Frank!

I can't believe it was just a little asterik "*". It's working goo
now.

- Larry
 
Back
Top