Excel - Sum by 1

[BNPMike]

I have the following data with the rows showing hours individuals have worked by columns showing the week-start date. The source data is split into a task breakdown so the same person can appear more than once. I need to consolidate the information. I would like to do something like SUM 1/4/11, 8/4/11, 15/4/11 BY PERSON.

Person___1/4/11___8/4/11___15/4/11
Pers_A_____5_______5_________5
Pers_B_____10______0_________2
Pers_C_____8_______20________0
Pers_A_____7_______0_________3
Pers_D_____12______12________12
Pers_B_____12______5_________7

If the data is re-formatted as a relation (list), with columns Person, Hours, Date, I can use a pivot table but it looks like pivot tables don’t like matrices.

Any suggestions?

 

[GlennUK]

Cross posted:

http://www.mrexcel.com/forum/showthread.php?t=554330

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

The other solution: pivot table still works!:
1. pt wizard, step 1, select consolidation as source type,
2. pt wizard, step 2a, the user will add a page field,
3. pt wizard, step 2b, add data range, no (0) page fields,
4. pt wizard, step 3, layout: drag 'column' onto row area (or rearrange row/column fields according to your needs).

combo

 

[BNPMike]

Combo

That does indeed work - beautifully.

I have John Walkenbach's Excel 2003 Bible which is "100% Comprehensive".

But it isn't, in case you didn't already know.

 

[combo]

Oh, there are some apocryphal books not included in the canon.

combo

 

[SkipVought]

We only recognize 'the received text.'

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}