How to display the sum of a calculation based on multiple rows

Hey guys,

I am trying to calculate cell values of different rows (e.g. B12 + C1) but I think the issue is that it doesn't know where to write the resulting value. It works perfectly fine as long as I am using the same row but it doesn't show the result once it's different rows.

Thank you for your advice in advance!

Help

Expression

Accepted answers

All comments

FrederikE

@Cela, What exactly do you mean - what is "it", Also what are you referring to with B12 and C1, cells like in Excel? - Alteryx doesn't function that way. Which Tool do you use?

In Alteryx formulas can only work on row level, unless you use special tools like the Mutli-Row tool.

Cela

@FrederikE Thanks for the quick response! I am basically using the Multi-Row-Formula Tool to calculate with values of two rows in the same column. The values are numeric so I already considered to set that column to a numeric type.

The calculation itself is easy: I refer to a specific value in Column A to grab the value of Column B in the same row and do the same thing for e.g. the row below that one. The result is displayed as long as the calculation refers to values of the same row but gets confused as soon as I refer to values of different rows.

cjaneczko

Can you screenshot an example? B12 and C1 are not in the same row or column, those are two references that don't share anything in common.

Cela

@cjaneczko I can see how this might be confusing, sorry about that. It was merely an example, but my case involves simply numbers written under one another.

Basically:

Column A Column B

Text 1 123,456

Text 2 789,012

And as I said, I grab those numbers by referring to Column A first and picking those numbers next.

cjaneczko

The summarization tool can do this. Group by Column A and sum Column B.

Cela

Here's a screenshot of the table and the output after using Multi-Field-Row trying to calculate with both rows

That's what it shows once I refer to one row only (subtracted the value from itself so that it results in 0)

cjaneczko

Here is the output.

Cela

@cjaneczko Thank you for the recommendation! I'm afraid that this won't cut it since I have multiple rows in the original input that are not supposed to be calculated as a whole. It requires me to calculate specific rows instead of the entire column.

cjaneczko

The only other way I can think of this is create a filter and filter the rows you want to total. Sum them up with the summarize tool and then union them back into the original sheet. Say you dont want Text 3 Summarized, then create a filter than filters Text 3 out. Then summarize Text 1 and Text 2. Once those are summed Union the false side of the filter with the True side of the filter to bring them back together summed up.

Cela

@cjaneczko Thank you for the recommendation! That would be the solution using "brute force" I guess. It would become complicated at some point though because there are 50+ rows per table and breaking those up to glue them back together would require a lot of time, so I see this as some sort of a "last resort".

Isn't there a way to communicate to the WF that it should write the result in a specific row?

cjaneczko

Possibly. I'd need to see a larger data set example of the final output. If the data is always the same 50+ records with no new entries you can use a reference input for the ones you want summed up with a combination of Record ID to set the order properly of where the summarizations are to be placed.

Cela

@cjaneczko I would like to refrain from using the Summarize Tool just yet. It really is just a case of (just an example!) "Row 1 and Row 5, 9 and 39 shall be calculated as Row 1 - Row 5 + Row 9 - Row 39" and put the result into a specific cell or anywhere visible, really.

I'm sure there must be a way to make that work since there is no error message in the expression and I tested the expression successfully by calculating the same row with itself.

Hope this helps to clarify where I am trying to head to with this WF.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703