# finding the sum and average up to the last row

#### ellen0804

##### New Member
Hi!
I'm working on a macro in which I want to find the sum, average and median of a column up to the last row. I have defined a variable to find the last row:

'Find the last row
Range("L32").Select
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row

Range("L" & LastRow).Select
ActiveCell.Offset(1, 0).Select

This last row thing works great in earlier places in the code. Now, later in the code I want to use this variable to find the sum and the mean and median of a couple columns from row 2 to the last row. Here's what I have, but it only sums the first 30-ish rows:

'Fill out the number correct, total number of trials, reaction time and solution time
Range("S7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[-2]:RC[-2])"
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(R[-6]C[-8]:RC[-8], ""C"") + COUNTIF(R[-6]C[-8]:RC[-8], ""E"") + COUNTIF(R[-6]C[-8]:RC[-8], ""SC"")"
Range("S9").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-7]C[-4]:RC[-4])"
Range("S10").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-8]C[-4]:RC[-4])"
Range("S11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-9]C[-3]:RC[-3])"
Range("S12").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-10]C[-3]:RC[-3])"
Range("S13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-11]C[-5]:RC[-5])"
Range("S14").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(R[-12]C[-5]:RC[-5])"

I've tried to do something like this (for the sum, median and average commands):

ActiveCell.FormulaR1C1 = "=SUM("R2:R"&LastRow)"

But it gives me a compile error? Am I missing something? And can I do this for sum, median and average?
Thanks so much for your help! I'm clearly still very new to writing code! ### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### JackDanIce

##### Well-known Member
ActiveCell.FormulaR1C1 = "=SUM("R2:R"&LastRow)"
Try:
Code:
``ActiveCell.Formula = "=SUM(R2:R" & LastRow & ")"``
Also, for your LastRow, you could use this instead (saves you having to remember what the last row number is:
Code:
``LastRow = Range("A" & Rows.Count).End(xlUp).Row``

#### VoG

##### Legend
Try like this

Code:
``ActiveCell.Formula = "=SUM(R2:R" & LastRow & ")"``

#### ellen0804

##### New Member
Wow! That was so super fast! Thanks so much! It seems to be working, however, it's giving me a #NAME? error when I run the macro. When I click on the cell this is the formula it's giving me:

=AVERAGE('P2':'P74')

And when I remove the ' ' it runs just fine. Any suggestions?

Thanks again! I'm learning so much! #### VoG

##### Legend

What is your code for the average?

#### ellen0804

##### New Member
Oh! Sorry -- it's doing it for all of these:

'Fill out the number correct, total number of trials, reaction time and solution time
Range("T7").Select
ActiveCell.Formula = "=SUM(Q2:Q" & LastRow & ")"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(K2:K" & LastRow & ", ""C"") + COUNTIF(K2:K" & LastRow & ", ""E"") + COUNTIF(K2:K" & LastRow & ", ""SC"")"
Range("T9").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(O2:O" & LastRow & ")"
Range("T10").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(O2:O" & LastRow & ")"
Range("T11").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(P2:P" & LastRow & ")"
Range("T12").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(P2:P" & LastRow & ")"
Range("T13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(N2:N" & LastRow & ")"
Range("T14").Select
ActiveCell.FormulaR1C1 = "=MEDIAN(N2:N" & LastRow & ")"

#### VoG

##### Legend
Make sure that the destination cells are formatted as General then try again.

#### ellen0804

##### New Member
Thanks! That didn't work, but I realized I had R1C1 at the end of the statement to the left of the equal sign. When I removed those it worked just fine. Thanks again super much for your help!!  Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,277
Messages
5,836,343
Members
430,421
Latest member
Natas ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back