I am attempting to write a SUM formula in Excel for an entire column. The issue I am running into is that the cell I want to place the formula in (at the end of the data) is variable based on the amount of information in the spreadsheet. How do I write a SUM formula when the cell is not static?
Hi swarley,
There’s actually a formula that can be used when you don’t know the range. =SUM(INDIRECT(ADDRESS(1,COLUMN())&“:”&ADDRESS(ROW()-1,COLUMN())))
So that can be placed anywhere and it sums up everything above it.
I think your range is wrong. Looks like you have extra quotations.
“A1” is your normal range so you are just replacing the 1 with a variable, like…
“B”+variable
and “variable” can be the .Count of the rows, so …
“B”+(dt.Rows.Count+2).ToString
with “dt” being the variable of your dataTable
If this doesn’t help resolve it, then let us know what exception you get.
Just a small Input Just take your Excel Data from Excel by using Excel application scope and Read range activity then fire the below linq piece of code to take the sum out of it.
and add the new row in the the datatable and write it in your Excel . Every time with range you don’t have to deal with
I am creating a Read Range activity from “B12:B6000”. Then I am creating a Write Cell activity and the range is defined as “B12”+(vRowCount.Rows.Count+2).ToString and the formula is defined as “=SUM(INDIRECT(ADDRESS(1,COLUMN())&”“”“:”“”“&ADDRESS(ROW()-1,COLUMN())))”
Below is the exception. Thanks again guys. I’ll try your technique Aksh if I understand it
You could try hardcoding your range to test it with “B6002”
You will need to make sure that vRowCount.Rows.Count gives you the correct value before that activity.
Your range field should look like this:
“B”+(vRowCount.Rows.Count+2).ToString
which would be “B6002” assuming the .Rows.Count is correct.
Thanks.
the LINQ methods are awesome too, but keep in mind if you want to have ability to edit table manually, then the SUM will be wrong as you insert rows into the file since it won’t be using a formula. So depends on your goals I guess.
I feel like my Read Range activity is incorrect in creating the variable. Do I need to go into the variable and add a VB expression? If this doesn’t work then I might just look for a work around.
I’ve attached screenshots of my current sequence. Could you please take a look and see if you notice anything out of the ordinary. I’ve created a test Excel spreadsheet that has information going from D1-D2000 (all numeric). If you don’t see anything wrong then I thank you for your time and I’ll attempt another solution.
**Created a variable by doing ctrl-k in Read Range activity
Everything looks good to me. And you have the correct Sheet name?
I would place a Write Line or Message Box after Read Range and output
“D”+(vCountRows.Rows.Count+2).ToString
That should give you the Cell you want to write it to, and it will tell you it found data.