Sum of Hours Per Month

[Zachary Baker]

I have been given a data set which contains work hours and dates in the form
of mm/dd/yy. I am trying to sum all the hours worked in each month. A
co-worker suggested I use sumproduct but I'm still not able to achieve the
process. Additionally, the data crosses over years, so I am not quite sure
how to create a fomula that takes the year into account. Any help would be
great. Thanks much.

--
Zachary Baker

[Bob Umlas]

Using a pivot table grouped by months and years would be the most
straightforward.

"Zachary Baker" <Zachary [email protected]> wrote in message
news:[email protected]...
> I have been given a data set which contains work hours and dates in the
form
> of mm/dd/yy. I am trying to sum all the hours worked in each month. A
> co-worker suggested I use sumproduct but I'm still not able to achieve the
> process. Additionally, the data crosses over years, so I am not quite
sure
> how to create a fomula that takes the year into account. Any help would
be
> great. Thanks much.
>
> --
> Zachary Baker

[Bernie Deitrick]

Zachary,

For example, to get the values for January of this year, with dates in column A and hours in B:

=SUMPRODUCT((A1:A2000>=DATE(2006,1,1))*(A1:A2000<DATE(2006,2,1))*B1:B2000)

Easier would be a pivot table, with the data grouped based on months.

HTH,
Bernie
MS Excel MVP


"Zachary Baker" <Zachary [email protected]> wrote in message
news:[email protected]...
>I have been given a data set which contains work hours and dates in the form
> of mm/dd/yy. I am trying to sum all the hours worked in each month. A
> co-worker suggested I use sumproduct but I'm still not able to achieve the
> process. Additionally, the data crosses over years, so I am not quite sure
> how to create a fomula that takes the year into account. Any help would be
> great. Thanks much.
>
> --
> Zachary Baker

[bpeltzer]

I'll suppose you have a date in column A and hours in column B.
To calculate the hours worked in May '06:
=sumif(a:a,">="&date(2006,5,1),b:b)-sumif(a:a,">="&date(2006,6,1),b:b)
The first part adds all hours worked on/after May 1 '06; the second part
subtracts out all hours worked on/after June 1 '06, leaving only those worked
during the month of May '06.

"Zachary Baker" wrote:

> I have been given a data set which contains work hours and dates in the form
> of mm/dd/yy. I am trying to sum all the hours worked in each month. A
> co-worker suggested I use sumproduct but I'm still not able to achieve the
> process. Additionally, the data crosses over years, so I am not quite sure
> how to create a fomula that takes the year into account. Any help would be
> great. Thanks much.
>
> --
> Zachary Baker