Total number of Jan 2007 entries in a list of date formatted field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that captures work done by sales reps. For each entry there
exists a date. This report is exported in Excel format and the date is
captured as mm/dd/yyyy hh:mm. I would like to have a column that lists the
number of work entries by Jan 2007, Feb 2007, Mar 2007, etc.

I am at a loss as to how I can conditionally count the occurrence based on
the month and the year in this date field.

Thanks in advance.
 
=SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where B2:B200 holds the dates


you can expand on that if you want to count for an individual


=SUMPRODUCT(--(A2:A200="Joe"),--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where it would count Joe's Jan 2007 sales
 
Thanks. That is EXACTLY what I needed.

Peo Sjoblom said:
=SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where B2:B200 holds the dates


you can expand on that if you want to count for an individual


=SUMPRODUCT(--(A2:A200="Joe"),--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1))

where it would count Joe's Jan 2007 sales
Click to expand...
 
Back
Top