Add up multiple columns per specific numbers

Hello,
I want to add up multiple columns depending on the number in a specific row.
If I want to add up just the columns with a specific number (e.g. every column with a “7” in the range D3:AE3) then everything works

But now I want to add up: Example:
all columns with the number “7” in the Range D3:AE3 (sheet “update”) and write them into column D in sheet2
then I want to use a counter, add 1 and add up all columns with the number “8”

the formula I use to do this: =SUMIFS(update!D:AE, update!D:AE, “”)
*note: I saved the formula in a text file

My thoughts about this code:
I saved the columns in an array. After that I used a “while” to loop through every column in row 3 to get the number in each cell. Inside the while I used an “If” to check if the cell contains the same number as my counter c3. If not, the counter adds 1 until cell and counter have the same number. When they have the same number (e.g. 7), I used a “for each row” with an “if” inside to add up each row until it reaches an empty row.
I also inserted two “assign” to: ~go to the next column and ~to search for the next number (8)

If I start the application the counter c3 won’t stop to add 1 even if the cell and the counter have the number 7.
Does anyone knows what’s the problem about this?

The excel sheet:
before:

after:

@Loons Can you explain the Output Sheet that you have shown us? It is confusing as the values don’t match directly with the Input sheet.

1 Like

@Loons I think I was able to get the Output in the format that you needed. But it uses an additional Package which is mentioned Below :

It might be a bit difficult to understand the workflow, so I have added a bit of annotations so that you may understand it. But if you still have questions, you can ask me.
Check the workflow :
GroupByAndSum.zip (10.4 KB)

Note : The file that I have used is what you had provided as an example in another post. So considering that file is still the type of data that you need to perform operation on, I have added the required logic.
But I was able to get the Output for that file.
If you get any errors please revert back with Screenshots.

1 Like

Thank you very much for your help!

I tried to start it, but I always get this error:

image

1 Like

@Loons Have you changed any data ? Are you using the Input file present in the file which I provided or your own file ?

1 Like

No, I haven’t changed anything…
And I also used the excel file that you’ve sent

@Loons Yes, Even I get that error, That maybe because of the Empty Columns that are present in Excel. Are those Empty Columns present in the Input data as well?

1 Like

The original input sheet looks like this:

So if I delete the first column in the example sheet, it works perfectly :slight_smile:
Thank you :slight_smile:

1 Like

@Loons So do you need a modification or you got the solution that you expected :sweat_smile: ?

If I start the workflow using another file, the Output is wrong…

The file I used:
Original file.xlsx (15.5 KB)

You will see, the Output looks like this:

@Loons Can you tell what is actually wrong in it? So that i can cross check and make it more generalise if possible

The Output in my other file (Original file) looks like copy and paste from sheet1. So, the rows aren’t add up per month like it should. (Like the picture in my previous message)

In your Example file the output looks like this:
image

Like I said, the Output in the other file doesn’t look like this.
But it also should look like the Output from your Example file. Like this:
image

The difference now is, that I want to add up the columns with “7” and “8” and not 5 and 6 like in the example sheet. The numbers can be different in every sheet

You understand? :thinking:

@Loons Yes. I guess I figured out the problem. In the Original Excel File, as you can see, at the Beginning there is an empty row, When the Read Range Activity is used to get that Data in a Datatable, that row is not empty in datatable, it actually contains the same date value as in the next row, and hence the columns get jumbled and the bot didn’t get the right Column to be grouped.

What I have done is :

  1. Delete the Empty Starting row in Excel, Then Execute the workflow. It actually gave me the output needed.

out

But if you think that is the same situation always and you want to handle it as well, then we would need to add some additional steps to it.

1 Like

Just figured out my file is going to look like this:
Testfile.xlsx (29.2 KB)

As you see, column B is filled with “test”. But the Output still should look like our previous Output… So, without column B

And if I start the workflow with this file, I get this error.
image

@Loons Yes. That’s why we would need to have the Exact format of the excel to work on :sweat_smile:, the confidential data can be replaced by some dummy data, but format should match the required. Otherwise it create such errors, So this is the final format that you’ll be working on right?

1 Like

The original file has got more than 500 rows…But this can be different every time :thinking:

But everything else is in the right format yes :sweat_smile:

That’s the file with all rows:
Testfile.xlsx (185.5 KB)

@supermanPunch
Hello again,
If I modify the “original file” that it looks like the “testfile” (which is the official file) than everything works.
But if I use the file “testfile” I get the Error ‘Column 11’ does not belong to table.
So, both files look the same, but only one works… Can you please tell me why this happens?

@Loons Yes. Sorry, I forgot about that problem, I was busy with something else :sweat_smile:,
I have already told the reason, but I don’t actually know why it happens, when you read the original file. The empty row at the beginning of the excel, in the datatable, is not empty. It takes the values of the previous rows, and hence it doesn’t recognise the column names that are actually needed to be grouped and Summed up.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.