Count hours per month

[Guest]

Hi All

I have a spreadsheet where i need to count the number of hours and minutes
spent working on products per month

In cell range 'H' i have the time spent working i.e. 00:40 (40 minutes)

In cell range 'P' i have the dates i.e. 15/09/07

How do i calculate how many hours and minutes were spent working on products
during Jan,Feb,Apr etc?

I have tried this formula, but it only works for some months (works in
January, but not in September)

=SUMPRODUCT(--(TEXT('With costs'!P25000,"mm yyyy")="08 2007"),--('With
costs'!H2:H5000))

Many thanks

Derek

 

[Pete_UK]

You could try this:

put the numbers 1 to 12 in cells X1:X12, then in Z1 enter this
formula:

=SUMPRODUCT((MONTH('With costs'!P$2$5000)=X1)*('With costs'!H$2:H
$5000))

and copy the formula down into Z2:Z12. If you want to distinguish
between different years then put 2007 in Y1:Y12 and amend the formula
to:

=SUMPRODUCT((MONTH('With costs'!P$2$5000)=X1)*(YEAR('With costs'!P
$2$5000)=Y1)*('With costs'!H$2:H$5000))

and copy down.

Hope this helps.

Pete

 

[Guest]

Thank you Pete, ill try that

You could try this:

put the numbers 1 to 12 in cells X1:X12, then in Z1 enter this
formula:

=SUMPRODUCT((MONTH('With costs'!P$2$5000)=X1)*('With costs'!H$2:H
$5000))

and copy the formula down into Z2:Z12. If you want to distinguish
between different years then put 2007 in Y1:Y12 and amend the formula
to:

=SUMPRODUCT((MONTH('With costs'!P$2$5000)=X1)*(YEAR('With costs'!P
$2$5000)=Y1)*('With costs'!H$2:H$5000))

and copy down.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Derek - hope it works for you.

Pete