Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Summing data from Multiple columns into a new calculated column

kzaveri19
6 - Meteoroid

Hello friends, 

 

What is the best way to sum values from multiple columns, from an Excel file, into a new column? If it is an expression, could you please show an example? 

 

Thank you!

10 REPLIES 10
danrh
13 - Pulsar

I would use the Formula tool. Place it after your Input Data tool, then from the drop-down in the Formula tool's configuration select "+ Add Column" and type the name for your new column. For the expression, use a formula to add them together:

 

image.png

Make sure to choose an appropriate Data type (i.e. Int16/32/64, Float, Double, etc) so you can use it later in your flow.

 

Hope this helps!

Dan

slee
6 - Meteoroid

I also have a similar question...I currently have 10+ columns that I would like to sum up. Is there a way to do it without having to manually put in each field name into the formula tool? 

danrh
13 - Pulsar

Depending on your data structure, you could use a Transpose and then a Summarize.  This would allow you to select the fields you want to keep and sum the rest together.

kzaveri19
6 - Meteoroid

Thanks guys!! All excellent solutions. I will try them out and see which one works best for me 

GaRaGe
8 - Asteroid

I have a similar requirement with more than 100 columns that i need to sum up. But i do not want to do a transpose & summarize. Is there any other way of doing it efficiently?

bharti_dalal
10 - Fireball

Hi @GaRaGe,

why you don't want to use transpose?  I think you want to see your sumed field along with other 100 fields, for that you can use join tool and achieve the desire result. i am attaching the solved workflow. See if it helps.(As an example I am taking 20 fields, you can do this same on any number of fields)

Screenshot (131).png

Thanks,

Bharti

GaRaGe
8 - Asteroid

thanks @bharti_dalal but wont it be more complex when the number of fields and rows are really huge in number?

bharti_dalal
10 - Fireball

@GaRaGe According to me ,this is the most simple method when the number of rows and fields are more. Try it out, you will get to know. Thanks.

Jack_Selfridge
5 - Atom

Thanks this was the exact solution I was looking for.

Labels