Excel 2007 : Calulation of how many days in column.

**losthero** · 03-21-2010, 08:59 PM

Need help trying to work out how to get my spreadsheet to work out how many jobs were opened in a month.

I have column A as the date the job was initiated using date format of 14-FEB-10 (DD-MMM-YY). On Worksheet 2 I would like to have the number of jobs to be displayed automatically for each month.

Can someone provide me with a formula?

Greatly appreciated.

**teylyn** · 03-21-2010, 09:08 PM

Hello losthero,

Your best option would probably be a pivot table.

A slower alternative can be achieved with SUMPRODUCT formulae. On sheet2 you can have a list of dates, 1-Jan-2010, 1-Feb-2010, etc. If you want format them to show only month and year. Then in B2 put this formula

=sumproduct(--(month(Sheet1!$A$1:$A$1000)=month(A1)),--(year(Sheet1!$A$1:$A$1000)=year(A1)))

cheers

copy down

**losthero** · 03-21-2010, 09:34 PM

Thank you. I am not quite sure how to get the SUMPRODUCT to work. Can you help break it down for me?

Sheet 1 Column A I have the dates listed Eg: I have 8 jobs in Jun 2008, 3 jobs in Jul 2008. On Sheet 2 Column C I have the Months listed to display the number of jobs for that month.

When I copied that formula you provided it came up with an error. Not quite sure where to look to fix it.

Thank you.

**teylyn** · 03-21-2010, 09:44 PM

On Sheet 2 enter a date like 1-Jun-2008. Format it to look any way you like, but it must be a date, not a text or a number. Then the formula will work.

See attached