Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Using SUM In a Macro.

Using SUM In a Macro

by Allen Wyatt
(last updated September 30, 2019)


Bob has a need to use the SUM function in a macro in order to find the sum of all the values in a column. The problem is that the number of cells to be summed will vary; for one run of the macro it could be 100 cells, while on the next it could be 300 and on the third only 25.

First, it is easy to use most worksheet functions (such as SUM) from within a macro. All you need to do is to preface the function name with "Application.WorksheetFunction." or simply "WorksheetFunction." Thus, if you know that each run of the macro will require summing A1:A100, then A1:A300, and finally A1:A25, you could use a macro like this:

Public Sub Sum_Demo()
    Dim myRange
    Dim Results
    Dim Run As Long

    For Run = 1 To 3
        Select Case Run
        Case 1
            myRange = Worksheets("Sheet1").Range("A1", "A100")
        Case 2
            myRange = Worksheets("Sheet1").Range("A1", "A300")
        Case 3
            myRange = Worksheets("Sheet1").Range("A1", "A25")
        End Select
        Results = WorksheetFunction.Sum(myRange)
        Range("B" & Run) = Results
    Next Run
End Sub

This macro uses a For . . . Next loop to specify different ranges of cells to be summed. It then uses the SUM worksheet function to assign the sum to the Results variable, which is (finally) stuffed into a cell in column B. The results of the first run are put in B1, the second in B2, and the third in B3.

While this particular macro may not be that useful, it shows several helpful techniques, such as how to define a named range, how to use the SUM function, and how to stuff the sum into a cell. What the macro doesn't do is to show how to select a variable number of cells to be summed. To do this, it is best to rely upon the End method of the Range object. The following code line shows how you can stuff the sum of the range starting at A1 and extending to just before the first blank cell in the column:

myRange = ActiveSheet.Range("A1", Range("A1").End(xlDown))
Range("B1") = WorksheetFunction.Sum(myRange)

Note that a range (myRange) is defined as beginning with A1 and extending through whatever the End method returns. This is then summed and stuffed into B1.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3217) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using SUM In a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Searching for ASCII and ANSI Characters

Need to get down to the "character level" when searching for information in a document? Word allows you to search for any ...

Discover More

Setting Up Your Printer

Word allows you to take full advantage of the capabilities of your printer. Accessing those capabilities is done through ...

Discover More

Determining the Current Directory

When creating macros, it is often necessary to know which directory is the default. Here's how you can find out by using ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Changing the Default Drive

Do you have a macro that need to read and write files? If so, then there is a good chance you need to specify the default ...

Discover More

Creating a String in a Macro

Need to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...

Discover More

Engineering Calculations

Need to normalize your data in some way so that all your values are in a given format? This tip presents a number of ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 2?

2020-08-28 06:13:00

Alan Elston

The Oops macro seemed to solve it! … --------- Alan Elston

2020-08-28 02:56:58

Anshu Kumar

Thank everyone for helping me.
I've found my solution on excelfox website.
I'm not allowed here to post my reply with the website link. I don't know why.
Anyway, thanks!

Finally SOLVED!

2020-08-27 05:23:33

Willy Vanhaelen

@Anshu Kumar
Possibly your macro is in a module and at the moment you run the macro another sheet than the one containing your data is active. If that is the case use this macro:

Sub add()
With Worksheets("sheet1")
.Range("C2").Value = .Range("A2").Value + .Range("B2").Value
End With
End Sub

2020-08-26 04:01:14

Alan Elston

Hi Anshu
That basic formula/ code line of yours will usually work.
To help further I think we would need to see a sample workbook where it is not working for you.

Alan Elston

2020-08-25 20:23:42

Anshu Kumar

Hello Sir!
I want to add the value of two cell to get output in 3rd cell with the help of VBA but the following code do not work. Would you please help.

Sub add()
Range("C2").Value = Range("A2").Value + Range("B2").Value
End Sub

2019-02-25 07:43:57

Willy Vanhaelen

This should do the job:

Set endR = ActiveSheet.Range("F1").End(xlDown)
Set myrange = ActiveSheet.Range("F2", endR)
endR.Offset(2, 0) = WorksheetFunction.Sum(myrange)

2019-02-23 23:54:34


Hi friends I am having issues with this code :

myRange = ActiveSheet.Range("F1", Range("F1").End(xlDown))
Range("F18") = WorksheetFunction.Sum(myRange)

I want to create a macro which should tell excel that sum up range from F2 to (depends when data ends ) and give me result in the same column but after leaving one black cell.

for example my I want to sum up everything from F2 to F15 and then leave one row and give me that sum value in F17.

the problem with query mentioned above is every time my data could differ let's say sometime it can be F2 to F10 or F3 to F11 but once that data finishes at whatever row assuming F11 then leave one row and give me result in F13.

2017-05-17 04:59:12

Mannu Prajapati

simple and easy trick. thanks

2017-02-23 03:43:48


In its simplest form, you could have, for example, Loads in Column A and Factors in Column B. The Tonnage could be entered in Column C with a simple formula like =(A2*B2) and copied down the column. Similarly, you could have a macro place either the formula or the result into Column C.

What you didn't specify is whether you want the macro to run every time, and as soon as, a value is entered, or if you want to manually run the macro at the click of a button. I'm guessing the former, as you used the word "automatically".

This is where it gets a bit tricky. You said that you would like the entered loads to be replaced by the calculated tonnage. That's easy to do with a macro, but the danger is that the macro will recalculate the product of the two cells each time it is run, unless you have some form of indicator to show that the calculation has been performed and should not be repeated. So, how would you do that? You could use another cell in the same row as the calculation which is checked by the macro before it calculates the tonnage. Perhaps you could insert the letter 'T' in the same cell as the tonnage value, but this will adversely affect deriving totals at the bottom of the column. After the data entry has finished, you would need to be able to tell which is a Load value and which is Tonnage, which might be difficult, especially without the 'T'. What about your Column header (title)? Should it be "Loads", "Tonnage" or even "Loads/Tonnage"?

In other words, mixing values in the same column could lead to potential difficulties.

Another option when entering data is to interact with the spreadsheet user with something like an Input Box, where you prompt for Loads and Factors and have the macro write Tonnage and Factor values to the spreadsheet. There are lots of other options too.

Could I therefore respectfully suggest that you think a little more about how the data collection and presentation should work. Once you have that decision made, the writing of a macro-based solution becomes a whole lot simpler!

I hope that I have been of some help.

2017-02-22 12:32:07

Nick Davis

I am pretty new to creating macros and even complex formulas in excel but I am trying to update an inventory spreadsheet and I would like to streamline the calculating process. I would like to create a macro for Excel 2010, in which the macro will automatically calculate a number of Haul Truck Loads entered into a cell times a Load Factor and replace the entered loads with a calculated tonnage. Is this possible?

2016-11-15 06:23:56



You could adapt the code that I wrote and posted in June 2015. Assign the value of Retention to the variable 'r' and away you go!

Adjust the formula for Column and starting Row to suit your needs.

2016-11-14 11:42:50


Hello there,

is there a macro that will sum up a variable number of cells in a table (from 1 to 5 depending on another cell Retention)?
Thanks in advance.

2016-10-14 07:41:21



I would suggest that you just use the formula option in conditional formatting. This will save you from the issues surrounding the use of macros.

2016-10-13 05:57:43



Here is a very basic macro that will do what you need. It assumes that the data is in the range A1:C7. You will need to change the For statement to read your data. Don't be afraid to experiment!

Sub applycolour()
Dim i As Integer
For i = 1 To 7
If Cells(i, 3) < Cells(i, 1) + Cells(i, 2) Then
Cells(i, 3).Interior.Color = vbYellow
End If
Next i
End Sub

2016-10-13 01:34:24


i have an issue, kindly solve it.

have three columns ar A, B, and C.

issue is C should be in yellow color if C< A+B.

How will write in macros.
kindly help

2016-10-06 07:20:00


@Alan Legg

The following macro does what you want, but there is one proviso: You MUST put the text "end of list" (without quotes) at the bottom of the column of numbers. You may leave a space or two if you wish. If you prefer a different end-of-list identifier, then make sure to modify the code to match.

I hope you find this to be useful. There may be more elegant solutions, but at least I know that this one works!


Sub InsertSubTotals()
Dim i As Integer ' main loop counter
Dim j As Integer ' inner loop counter
Dim iRow As Integer ' initial number of rows in the list of numbers
Dim Tot As Integer ' subtotal value to be written

iRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
Tot = 0
i = 1
knt = 0

Do Until Range("D" & i).Value = "end of list"
If WorksheetFunction.IsNumber(Range("D" & i).Value) Then
For j = i To iRow
If WorksheetFunction.IsNumber(Range("D" & j).Value) Then
Tot = Tot + Range("D" & j).Value
Range("D" & i).Select
Selection.Insert shift:=xlDown
Range("D" & i).Value = Tot
Tot = 0
iRow = iRow + 1
i = j
Exit For
End If
Next j
End If
i = i + 1
End Sub

2016-10-05 20:38:38

Alan Legg

Can you write a macro to do the following all within a given column (say D): Starting at row 1, each time you encounter a cell containing a number (ex. D45) to sum all contiguous numbers until you encounter a blank cell (ex.D45:D48); then place that sum into the cell just above the first cell of that group (ex. D44). Note: the number of contiguous cells in each group will vary.

2016-10-05 02:45:40



VBA by its very nature is programming, so activity is mostly controlled within a loop, such as For, While, Do Until and so on. If you only have a single function to perform, a formula will normally suffice.

If I understand your question and example correctly, you could have VBA place an addition formula of the type that you suggest at the bottom of a column of data and copy it across as many columns as you need, but the simplest method is probably the SUM formula inserted manually rather than by a macro.

2016-09-22 15:50:50




A1:A3 = 1 1 1
B1:B3 = 1 1 1

C1:C3 = 2 2 2



2016-02-25 10:58:37


Try the following code:

Dim i As Integer
Dim irow As Integer

irow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To irow
Cells(i, 1) = Cells(i, 1) * 100 / 114
Next i

You will need to add a command button and assign the macro to it. It is'nt the refresh button but it's still only one click.

If you don't like Cells(i, 1), you can always substitute it with Range("A" & i) if you prefer.

2016-02-23 07:03:02


Hi there,

Is there a way to write a macro that can calculate columns in this order:

Column A (Price * 100 / 114), so whenever i open the sheet and click refresh it automatically calculate all values in Column A, multiply the values found in Column A by 100 and divide it by 114?

2015-06-29 03:30:51


... or this if you prefer!

Dim r As Integer

r = Range("B1").Value

Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & r))

2015-06-29 03:28:53


@ ulysis

Here are three lines of code that should do the trick. What the code does is:
a) define an integer variable
b) assign the value of cell B1 to the variable
c) SUM the contents of column A from A1 to the selected row and put the answer into the cell of your choice (in this case B3)

Dim r As Integer
r = Range("B1").Value
Range("B3").Value = WorksheetFunction.Sum(Range("A1:A" & Range("B1").Value))

2015-06-28 03:58:40


thanks for your useful tips..
but, i need vba to sumup certain rows based on a cell value
i.e. if cell b1 contains 6 then i want vba to sum cell a1 to a6 and if cell b1 contains 9 then vba to sum cell a1 to a9.

please help me on this

thanks in advance

2015-06-03 08:54:02


Holy wow Dave, talk about being the exact right thing I needed. Thanks so much man.

2015-05-04 16:39:55


Thank you so much Dave for your helpful comment

2015-04-27 02:30:34


I just needed a small sample about makro programming. So thank you for this helpful, short and compact example. very good!

2015-01-06 03:57:40


Here's an alternative if you want to add SUM to a cell when you don't know how many rows of data your macro will meet during any given run.

Assuming you want to add the values in Column D of your data, and put the total in the next row below, use the following two lines of code:

Range("D" & (x + 1)).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & knt & "]C:R[-1]C)"

This may look a little complicated to anyone not familiar with macros, so let's break it down into its components to make it easier to understand.

Variables x and knt are Integer (or Long if you prefer).
x is the loop counter, so it holds the current Row number.
knt is a count of the number of rows of data processed, which allows for your data not starting at Row 1. You must choose your own method of assigning a value to knt.

The formula uses the R1C1 convention to define the range to be summed. The range is defined as the current Row (R) minus the number of records processed for the current Column (C), down to the current Row (R) minus 1 for the current Column (C).

In other words, it starts at the selected cell and sums the cells from the one immediately above back up along the column for as many rows as were processed.

If you prefer to have a blank row between the data and the SUM cell, then select the cell given by (x+2) and change the portion of the SUM formula to R[-2] to match.

After the macro has run, you will see the SUM formula in the usual format in the cell, such as =SUM(D4:D127) .

This works nicely for me. I hope it helps you too.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.