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

Hi!

Assume your current table is in sheet1 A1:C7.

To create your new table:

Assume you want it on sheet2.

List the employee names in sheet2 A2 on down. I'd use an
Advanced Filter to copy the unique values.

In sheet2 A1 enter: Week Commencing

In sheet2 B1 enter: 2/21/2005
In sheet2 C1 enter: =B1+7
Copy across as needed

In sheet2 B2 enter this formula:

=SUMPRODUCT(--(Sheet1!$B$2:$B$7=$A2),--(Sheet1!
$A$2:$A$7>=B$1),--(Sheet1!$A$2:$A$7<B$1+7),Sheet1!
$C$2:$C$7)

Copy across then down as needed. Adjust references to suit.

You may have to tweak things once you reach the end of the
year/beginning of the next year depending on how you want
to handle it.

Biff

>-----Original Message-----
>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
>
>.
>