Sort and sum data in a table

T

TimW

I have a table of names, with multiple entries of each name. Each name has 5
columns of data that needs to be summed up.

If I use a pivot table, it does not allow me to have column A as the name, B
the sum of column B, C the sum of C, etc. It forces the data into individual
rows, which is not good.

Is there a way to create a list of unique names, and then sum up each column
of data for each of these names?

Thanks in advance
 
E

Earl Kiosterud

Tim,

Perhaps you need to put the name header (column A) into the row area of the
pivot table, and each of the B, C, etc., headers into the data area, for
totals of those columns for each unique name.

Describe your table, with examples. Pivot tables normally are good for
applications like this.
 
T

TimW

Table
a b c
user1 1 2 3
user2 1 2 3
user3 1 2 3
user1 2 2 2
user2 3 3 3
user3 4 4 4

What I'm looking for is
a b c
user1 3 4 5
user2 4 5 6
user3 5 6 7

The pivot table comes clost but organizes it like this: which is not
acceptable

user1
sum of a 3
sum of b 4
sum of c 5

What I've come up with so far is to use a vlookup to identify the first
occurence of each name. =VLOOKUP(DataWC!B8,DataWC!B$1:B7,1,FALSE)
This throws a #na when it is the first occurence, which I use to number and
identify unique names, which I then use another vlookup to compile into a
compact list elsewhere. Goofy, but it works well. I'd prefer to use a pivot
table though.

Thanks
 
D

Dave Peterson

Once you've built your pivottable and see:
sum of a 3
sum of b 4
sum of c 5
Click to expand...

Drag the grey button that says data directly over the Total cell (one cell to
the right).

You'll still see "sum of", but it'll look like you want.
 
E

Earl Kiosterud

Tim,

You can use Dave's suggestion, or you can roll your own with a bunch of
SUMIF formulas

For User1 - a, it would look like =SUMIF(A2:A7, "user1", B2:B7)
 
T

TimW

That's exactly it. Thanks!

Dave Peterson said:
Once you've built your pivottable and see:


Drag the grey button that says data directly over the Total cell (one cell to
the right).

You'll still see "sum of", but it'll look like you want.
Click to expand...
 
P

Patty

Sort the table by "Names". Choose Data from the menu,
then choose Sub-Totals. At each change choose "Names".
At Use Function choose Sum. Then at Add Sub-Total To
choose each column you wish to Sum. To the left you will
now have +/- which can be used to increase/decrease amount
of detail showing on the screen.
 
Top