New Total Column, sum of values across many columns per row

[JP02]

Hi all, 

This is probably such a simple problem, but besides a very manual formula, I am having a hard time figuring this out. If I have:

ID	COL1	COL2	COL3	COL4
123	0	0	1	0
456	1	3	0	1
789	0	5	0	1

And want:

ID	COL1	COL2	COL3	COL4	Total
123	0	0	1	0	1
456	1	3	0	1	5
789	0	5	0	1	6

What tool do I use to do this?  I have more than a 100 columns which need to by summarized in this way, so a manual [col1]+[col2}..etc. formula would be a nightmare!  Thanks!!

JP

[BenMoss · Accepted solution]

Firstly pivot your data, with the line ID acting as a key field and your value fields acting as your data fields.

Secondly summerize your data, grouping by the line ID and returning the SUM value.

Then use the join tool to bring your aggregated data inline with your oriinal data stream, using the Line ID field.

Ben

[JP02]

Ben, 

Works awesome! Thanks for helping me make sense of this problem.  Now that I see it in action, it makes sense.  Much appreciated!

JP

[dmccandless]

I'm just posting a file and screenshot w/ the solution @BenMoss described.