Excel 2003 Pivot Table Woes: Please Help!

**ryancochs** · 05-30-2012, 10:17 AM

Is it possible to aggregate data in a pivot table from different categories?

I have excel 2003.

For PURE illustration, I have 6 columns, A-F, respectively:

"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.

I want to see in a final output:

Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.

When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.

It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.

Any ideas to solve this?

Thank you much,

Ryan

**NBVC** · 05-30-2012, 10:37 AM

I am not sure if a pivot table is best for this... it would be good to see a sample report and what your expected output would be...

You may be able to use formulas for a solution....

**ryancochs** · 05-30-2012, 10:57 AM

Excel.JPG

Thank you.

See the attached image of what i was thinking of.

I realize i could use a sumif for Output 1 and countif for output 2, but there are multiple names (over 500 unique across the various 4 columns and 1,000s of rows in the real dataset) I would have to write out to make the output dynamic.

Please let me know if there is a simpler way.

Best,

Ryan

**NBVC** · 05-30-2012, 11:02 AM

What I can think of is temporarily copying the 4 person name columns into 1 column, then use advanced filter to get a unique list.. then use the SUMIF/COUNTIF formulas on that unique list.