Count names

[Guest]

I'm trying to count how many times a specific name appears on a date range
I have column "A" with Names and column "B" with Dates
For example I want to know how many "Joe" I have on May/05
Txs

 

[KL]

Hi Farrel,

Try this:

=SUMPRODUCT((A1:A10="Joe")*(MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))

Regards,
KL

 

[Guest]

One suggestion is to use "autofilter" and then you can sort by "Joe" and use
the "or" sort and enter "May 05". This would pull up all records meeting
those two criteria then you could use the "count" feature at the bottom to
count the number of records. This may not be the most expedient but if you
have a lot of records it will work.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A100="Joe"),--(B2:B100=--"2005-005-05"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:$A$100=E2),--($B$2:$B$100-DAY($B$2:$B$100)+1=F2))

where E2 houses a name like Joe and F2 the first day date of a
month/year interest like 1-May-05.

I'm trying to count how many times a specific name appears on a date range
I have column "A" with Names and column "B" with Dates
For example I want to know how many "Joe" I have on May/05
Txs

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.