Two dimensional sum by week

Hi,

I've got a problem relating to summing daily totals, by
an individual, by week.

Here's my source data:

Date Name Hrs
21/02/2005 John 11
22/02/2005 John 9
23/02/2005 George 7
23/02/2005 Fred 7.5
24/02/2005 John 9
24/02/2005 Fred 7.5
etc.

What I'd like to produce is a table something like this:

Week Commencing 21/2 28/2 7/3
John
George
Fred

And at the intersect, have the total hours worked during
that week. I guess some form of two dimensional SUMIF?

Any ideas?

Thanks,

Jon C

This looks like a good reason to learn pivottables.

Select your range
Data|Pivottable...
follow the wizard until you get to a step that has a "Layout" button on it.
Hit that Layout button.

Drag the header for the date field to the column Field
drag the header for Name to the row field
drag the header for hrs to the data field
If you don't see "SUM OF" in that data field, then double click on it and make
choose "Sum of"

Finish up the wizard.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Jon C wrote:
>
> Hi,
>
> I've got a problem relating to summing daily totals, by
> an individual, by week.
>
> Here's my source data:
>
> Date Name Hrs
> 21/02/2005 John 11
> 22/02/2005 John 9
> 23/02/2005 George 7
> 23/02/2005 Fred 7.5
> 24/02/2005 John 9
> 24/02/2005 Fred 7.5
> etc.
>
> What I'd like to produce is a table something like this:
>
> Week Commencing 21/2 28/2 7/3
> John
> George
> Fred
>
> And at the intersect, have the total hours worked during
> that week. I guess some form of two dimensional SUMIF?
>
> Any ideas?
>
> Thanks,
>
> Jon C

--

Dave Peterson

Thanks Dave but it's not that straight forward.

I should have explained better. My source data is on a
daily basis, i.e. hours per day per person. I need this
summed by person/week, not day as a pivottable would do.

Any other suggestions?

Jon C
>-----Original Message-----
>This looks like a good reason to learn pivottables.
>
>Select your range
>Data|Pivottable...
>follow the wizard until you get to a step that has
a "Layout" button on it.
>Hit that Layout button.
>
>Drag the header for the date field to the column Field
>drag the header for Name to the row field
>drag the header for hrs to the data field
>If you don't see "SUM OF" in that data field, then
double click on it and make
>choose "Sum of"
>
>Finish up the wizard.
>
>If you want to read more about the pivottable stuff, you
may want to look at
>some links:
>
>Debra Dalgleish's pictures at Jon Peltier's site:
>http://peltiertech.com/Excel/Pivots/pivottables.htm
>And Debra's own site:
>http://www.contextures.com/xlPivot01.html
>
>John Walkenbach also has some at:
>http://j-walk.com/ss/excel/files/general.htm
>(look for Tony Gwynn's Hit Database)
>
>Chip Pearson keeps Harald Staff's notes at:
>http://www.cpearson.com/excel/pivots.htm
>
>MS has some at (xl2000 and xl2002):
>http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
>http://office.microsoft.com/assistan.../articles/xlco
nPT101.aspx
>
>Jon C wrote:
>>
>> Hi,
>>
>> I've got a problem relating to summing daily totals, by
>> an individual, by week.
>>
>> Here's my source data:
>>
>> Date Name Hrs
>> 21/02/2005 John 11
>> 22/02/2005 John 9
>> 23/02/2005 George 7
>> 23/02/2005 Fred 7.5
>> 24/02/2005 John 9
>> 24/02/2005 Fred 7.5
>> etc.
>>
>> What I'd like to produce is a table something like
this:
>>
>> Week Commencing 21/2 28/2 7/3
>> John
>> George
>> Fred
>>
>> And at the intersect, have the total hours worked
during
>> that week. I guess some form of two dimensional SUMIF?
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Jon C
>
>--
>
>Dave Peterson
>.
>

I'd add a fourth column, labeled 'Week Commencing'. The formula in D2 would
be =a2 + 1 - weekday(a2).
That formula can be copied down to all your table rows. Then just create a
pivot table report, with 'Week Commencing' as your column header, 'Name' as
your row header, and 'Sum of Hours' as your data. (If you haven't used Pivot
tables, just highlight columns A through D, then go to the menu bar and
select Data, Pivot Table and Pivot Chart report...).
One caveat about Pivot tables: they won't automatically reflect updates to
your data. To refresh, right-click in the Pivot table and select refresh
data.


"Jon C" wrote:

> Hi,
>
> I've got a problem relating to summing daily totals, by
> an individual, by week.
>
> Here's my source data:
>
> Date Name Hrs
> 21/02/2005 John 11
> 22/02/2005 John 9
> 23/02/2005 George 7
> 23/02/2005 Fred 7.5
> 24/02/2005 John 9
> 24/02/2005 Fred 7.5
> etc.
>
> What I'd like to produce is a table something like this:
>
> Week Commencing 21/2 28/2 7/3
> John
> George
> Fred
>
> And at the intersect, have the total hours worked during
> that week. I guess some form of two dimensional SUMIF?
>
> Any ideas?
>
> Thanks,
>
> Jon C
>
>

Good idea. I'll give it a go.

Thanks.

>-----Original Message-----
>I'd add a fourth column, labeled 'Week Commencing'. The
formula in D2 would
>be =a2 + 1 - weekday(a2).
>That formula can be copied down to all your table rows.
Then just create a
>pivot table report, with 'Week Commencing' as your
column header, 'Name' as
>your row header, and 'Sum of Hours' as your data. (If
you haven't used Pivot
>tables, just highlight columns A through D, then go to
the menu bar and
>select Data, Pivot Table and Pivot Chart report...).
>One caveat about Pivot tables: they won't automatically
reflect updates to
>your data. To refresh, right-click in the Pivot table
and select refresh
>data.
>
>
>"Jon C" wrote:
>
>> Hi,
>>
>> I've got a problem relating to summing daily totals,
by
>> an individual, by week.
>>
>> Here's my source data:
>>
>> Date Name Hrs
>> 21/02/2005 John 11
>> 22/02/2005 John 9
>> 23/02/2005 George 7
>> 23/02/2005 Fred 7.5
>> 24/02/2005 John 9
>> 24/02/2005 Fred 7.5
>> etc.
>>
>> What I'd like to produce is a table something like
this:
>>
>> Week Commencing 21/2 28/2 7/3
>> John
>> George
>> Fred
>>
>> And at the intersect, have the total hours worked
during
>> that week. I guess some form of two dimensional SUMIF?
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Jon C
>>
>>
>.
>

Jon,
I think Dave's pivot table suggestion would still work. You would group the
dates by days and then it allows you to pick the numbers of days, which
would be 7.

Don

"Jon C" <[email protected]> wrote in message
news:[email protected]...
> Thanks Dave but it's not that straight forward.
>
> I should have explained better. My source data is on a
> daily basis, i.e. hours per day per person. I need this
> summed by person/week, not day as a pivottable would do.
>
> Any other suggestions?
>
> Jon C
>>-----Original Message-----
>>This looks like a good reason to learn pivottables.
>>
>>Select your range
>>Data|Pivottable...
>>follow the wizard until you get to a step that has
> a "Layout" button on it.
>>Hit that Layout button.
>>
>>Drag the header for the date field to the column Field
>>drag the header for Name to the row field
>>drag the header for hrs to the data field
>>If you don't see "SUM OF" in that data field, then
> double click on it and make
>>choose "Sum of"
>>
>>Finish up the wizard.
>>
>>If you want to read more about the pivottable stuff, you
> may want to look at
>>some links:
>>
>>Debra Dalgleish's pictures at Jon Peltier's site:
>>http://peltiertech.com/Excel/Pivots/pivottables.htm
>>And Debra's own site:
>>http://www.contextures.com/xlPivot01.html
>>
>>John Walkenbach also has some at:
>>http://j-walk.com/ss/excel/files/general.htm
>>(look for Tony Gwynn's Hit Database)
>>
>>Chip Pearson keeps Harald Staff's notes at:
>>http://www.cpearson.com/excel/pivots.htm
>>
>>MS has some at (xl2000 and xl2002):
>>http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
>>http://office.microsoft.com/assistan.../articles/xlco
> nPT101.aspx
>>
>>Jon C wrote:
>>>
>>> Hi,
>>>
>>> I've got a problem relating to summing daily totals, by
>>> an individual, by week.
>>>
>>> Here's my source data:
>>>
>>> Date Name Hrs
>>> 21/02/2005 John 11
>>> 22/02/2005 John 9
>>> 23/02/2005 George 7
>>> 23/02/2005 Fred 7.5
>>> 24/02/2005 John 9
>>> 24/02/2005 Fred 7.5
>>> etc.
>>>
>>> What I'd like to produce is a table something like
> this:
>>>
>>> Week Commencing 21/2 28/2 7/3
>>> John
>>> George
>>> Fred
>>>
>>> And at the intersect, have the total hours worked
> during
>>> that week. I guess some form of two dimensional SUMIF?
>>>
>>> Any ideas?
>>>
>>> Thanks,
>>>
>>> Jon C
>>
>>--
>>
>>Dave Peterson
>>.
>>

I missed your dates, but Jon suggested something that may make it a little
easier. I'd try that, too.

Jon C wrote:
>
> Good idea. I'll give it a go.
>
> Thanks.
>
> >-----Original Message-----
> >I'd add a fourth column, labeled 'Week Commencing'. The
> formula in D2 would
> >be =a2 + 1 - weekday(a2).
> >That formula can be copied down to all your table rows.
> Then just create a
> >pivot table report, with 'Week Commencing' as your
> column header, 'Name' as
> >your row header, and 'Sum of Hours' as your data. (If
> you haven't used Pivot
> >tables, just highlight columns A through D, then go to
> the menu bar and
> >select Data, Pivot Table and Pivot Chart report...).
> >One caveat about Pivot tables: they won't automatically
> reflect updates to
> >your data. To refresh, right-click in the Pivot table
> and select refresh
> >data.
> >
> >
> >"Jon C" wrote:
> >
> >> Hi,
> >>
> >> I've got a problem relating to summing daily totals,
> by
> >> an individual, by week.
> >>
> >> Here's my source data:
> >>
> >> Date Name Hrs
> >> 21/02/2005 John 11
> >> 22/02/2005 John 9
> >> 23/02/2005 George 7
> >> 23/02/2005 Fred 7.5
> >> 24/02/2005 John 9
> >> 24/02/2005 Fred 7.5
> >> etc.
> >>
> >> What I'd like to produce is a table something like
> this:
> >>
> >> Week Commencing 21/2 28/2 7/3
> >> John
> >> George
> >> Fred
> >>
> >> And at the intersect, have the total hours worked
> during
> >> that week. I guess some form of two dimensional SUMIF?
> >>
> >> Any ideas?
> >>
> >> Thanks,
> >>
> >> Jon C
> >>
> >>
> >.
> >

--

Dave Peterson