# Excel 2007 : Calulation of how many days in column.

1. ## Calulation of how many days in column.

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.

2. ## Re: Calulation of how many days in column.

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

3. ## Re: Calulation of how many days in column.

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.

4. ## Re: Calulation of how many days in column.

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

5. ## Re: Calulation of how many days in column.

Thanks, I can see that yours works. I needed to fiddle around for a little bit to get it to work. Thanks again for your help.

There are currently 1 users browsing this thread. (0 members and 1 guests)