Summing values across a row into a 'SUM' Column

1

Hi Everyone,

I am trying to add the values of multiple cells across a single row and have the result feed into a SUM column.

The data set looks something like:

ID,Type,Active,Jan1980,Feb1980,Jan2022,Feb2022
Aa,Full,Yes,0,10,5,0
Bb,Full,No,0,5,10,30
Cc,Partial,Yes,0,0,0,0

In the actual dataset all months and years from 1980 to 2022 are filled out. This makes it quite messy, but it is an unfortuante requirment of the software package for the data to be imported this way.

ID,Type,Active,Jan1980,Feb1980,Jan2022,Feb2022,SUM
Aa,Full,Yes,0,10,5,0,15
Bb,Full,No,20,5,10,0,35
Cc,Partial,Yes,0,0,0,0,0

Is there a way to achieve this?

So far, I have not had luck with aggregations or pivots; though I am likely to be missing something.

Thanks,

any help is much appreciated :slightly_smiling_face:

2

Hi Corey and welcome to the Community!

The sum column is basically an aggregate of multiple columns grouped by ID. So in this case, you need to:

  1. unpivot the table (keeping grouping by ID)
  2. keep only the values to aggregate
  3. sum them up (while grouped by ID)
  4. merge the sums back into the original table

See the sample project below.
column-sum.morph (4.9 KB)

image
image1429×591 18.9 KB

3

Thanks dgudkov, that worked a treat!