Sum across multiple Columns on a single row

[HBromell]

I am trying to sum across a few columns then subtract that value from the sum of another group of collumns.  For example- I want to sum the total of all fruit, sum all vegetables then get the difference. How would I do this in alteryx? 

Baskets       Apples      Oranges    Grapes     Zuchini   Spinach    

Combo 1     10.2             2.5             31            12           

Combo 2      null             10.5          null            45.5        12.2               

Combo 3      5                  7.5           12             null          31

Combo 4      7                 12.5            7              2.5          10 

[dataMack · Accepted solution]

If it's just thoe few columns, I would do it directly like this:  Drag a Formula tool onto the canvas connected to your data.  Pick some numeric data type (ex. int32) and give it a meaningful name, for example 'FruitLessVeggies'.  Your fomrula would be:

([apples]+[oranges]+[grapes])-([zuchini]+[spinach])

[HBromell]

I might have found a solution!  But would like some validation.  I used the multi Row formula to sum.  The data is looking correct.. But to complete the action I wanted I had to use it three times.. Is there a faster way? 

[HBromell]

Thank you!  In truth its more complicated than that, but that is the general idea.  I think some of my values downstream had an incorrect (non numeric) data type.. I have been banging my head for why the formula wasnt working.  But im guessing if I fix that it will be good.   

[danielbrun2]

Hi,

I would use transpose to have values in rows instead of columns. Then I would split the datastrems (in this case using a join tool). And finnaly summarice each stream and subtract them.

See attached example.

Best,

Daniel

[truct-kp]

This works well. 

I wish alteryx just create a simple column (numeric) sum function within the formula tool.