# 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?

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.

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:

Range might be something like:
â€śAâ€ť+(dt.Rows.Count+2).ToString

Regards

#5

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 . Every time with range you donâ€™t have to deal with

`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

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

#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

#11

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

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

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

I also get the same errror using this.

#20

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.