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?
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.
@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?
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:
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:
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
@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 :
Delete the Empty Starting row in Excel, Then Execute the workflow. It actually gave me the output needed.
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.
@Loons Yes. That’s why we would need to have the Exact format of the excel to work on , 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?
@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 ,
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.