Add sum of values in one column based on the value in another column

[JacobTwill]

Hi there,

I have an Excel file which looks like this, without the "added values" column:

What I want to do in Alteryx is: Add all of the values in the "values" column for each unique number in the "numbers" column.

The output should look something like the "added values" column, where all the values for each unique number have been added together. 

Appreciate any assistance!

All the best,

Jacob

[AngelosPachis]

Hi @JacobTwill 

Try using the summarize tool. You should group by Numbers and sum values. 

I think this will give you what you are looking for.

[JacobTwill]

Hi Angelos,

This is definitely what I need - thank you!

However, I now notice that my dataset does not list all the numbers for each row.

Instead, the "numbers" column contain nulls under each unique value.

Is there a way to replace the [nulls] with the numbers 0, 1, 2, and 3 respectively?

Appreciate the help!

Best regards

[AngelosPachis]

Yes there is,

In the attached workflow I have used a multi-row formula to populate the null fields. What happens is that the tool scans the numbers column from top to bottom. If a cell is populated by a value, it lets it as is, else if it's null, it takes the value from the row above.

Let me know if that worked for you.

Cheers

[JacobTwill]

This works for this dataset.

Thank you!

I have another dataset where the function does not work, however. This is because the new dataset does not start with a "0" value, but simply a [null] value, I assume:

Is this correct? And is there a simple way to achieve the same as in your previous reply, when the "numbers" column does not start with 0?

Best regards

[AngelosPachis · Accepted solution]

In that case you will have to tweak your multi-row formula tool to account for the fact that the dataset does not start with "0", as you correctly pointed out.

Try the following formula in your multi-row formula tool. It should do the job.

Let me know if you have any questions on the above. If that solution answers your question, please mark it as correct to close this thread 🙂 

Cheers

[JacobTwill]

This works.

Thank you!