Excel Variable SUM Formula


#1

Hi all,

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?

Thanks for your help all


How to write a formula in csv cell?
#2

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.


#3

Thanks Clayton for the quick response. Do I use the Write Cell activity? If so, what value do I place in the range field?


#4

That’s correct… Write Cell, and you might need to use the .Rows.Count of the datatable with the range.

The value will be the formula with embedded quotes:
"=SUM(INDIRECT(ADDRESS(1,COLUMN())&"""":""""&ADDRESS(ROW()-1,COLUMN())))"

Range might be something like:
“A”+(dt.Rows.Count+2).ToString

Regards


#5

image

Is this correct? I am running into an exception.


#6

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.

Thanks.


#7

Well

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 :slight_smile:. Every time with range you don’t have to deal with :slight_smile:

Int32 sum_col_2 = dt.AsEnumerable().Sum(function(r) r.Field(Of Int32)("Col2"))

or you can use even write cell with the sum_col_2 value :slight_smile:

Sample - datatble col sum.xaml (9.3 KB)

Regards…!!
Aksh


Error in storing double value in excel
Multiply two columns of a excel table
#8

Hi all,

Thanks for all the help - really appreciate it.

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 :slight_smile:


#9

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.


#10

How do I hardcode the range? Also, does it matter if the data are numbers?

This is the last part of the sequence and its so close to being finished that I can almost taste it :slight_smile:


#11

By hardcode, I meant just put in a range that only works with the test Excel file.
image

When you put in the Row.Count it should look like this:
image

Assuming your Sheet name is correct and that vRowCount is your dataTable variable.
(I only show errors cause I haven’t declared those variables.)

Also, verify that vRowCount.Rows.Count gives you the correct number before the Write Cell, if you still get Range Does not Exist.

Thanks.


#12

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.


#13

You could post your Read Range. Outputting the .Rows.Count of the datatable will tell you it found data.


#14

Clayton,

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


#15


#16


#17

Do I need to create a VB expression for the variable in the Read Range activity?


#18

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.

You don’t need a vb expression in the Read Range.

Thanks.


#19

HI @ClaytonM

I also get the same errror using this.


#20

Hi @aksh1yadav

OutputDT.AsEnumerable().Sum(function® r.Field(Of Double)(“Gross_Amount”))

When i use this method, i get the following error- Assign : Specified cast is not valid.