Two dimensional sum by week

[Jon C]

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]

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/assistance/2002/articles/xlconPT101.aspx

 

[Jon C]

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

 

[Guest]

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]

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.



.

 

[Don]

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

 

[Dave Peterson]

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