Count hours per month
 

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'!P2:P5000,"mm yyyy")="08 2007"),--('With
costs'!H2:H5000))

Many thanks

Derek

Count hours per month
 

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:P$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:P$5000)=X1)*(YEAR('With costs'!P
$2:P$5000)=Y1)*('With costs'!H$2:H$5000))

and copy down.

Hope this helps.

Pete

On Sep 19, 10:08 am, Derek wrote:
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'!P2:P5000,"mm yyyy")="08 2007"),--('With
costs'!H2:H5000))

Many thanks

Derek

Count hours per month
 

Thank you Pete, ill try that

"Pete_UK" wrote:

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:P$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:P$5000)=X1)*(YEAR('With costs'!P
$2:P$5000)=Y1)*('With costs'!H$2:H$5000))

and copy down.

Hope this helps.

Pete

On Sep 19, 10:08 am, Derek wrote:
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'!P2:P5000,"mm yyyy")="08 2007"),--('With
costs'!H2:H5000))

Many thanks

Derek

Count hours per month
 

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

Pete

On Sep 19, 10:44 am, Derek wrote:
Thank you Pete, ill try that



"Pete_UK" wrote:
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:P$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:P$5000)=X1)*(YEAR('With costs'!P
$2:P$5000)=Y1)*('With costs'!H$2:H$5000))

and copy down.

Hope this helps.

Pete

On Sep 19, 10:08 am, Derek wrote:
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'!P2:P5000,"mm yyyy")="08 2007"),--('With
costs'!H2:H5000))

Many thanks

Derek- Hide quoted text -

- Show quoted text -