Excel SUM Formula Hacks: 7+ Simple to Advanced Examples

Who doesn’t know, that the SUM function in Excel is a formula that can add up data? This formula can shorten the work of adding data without using equations.

Yes… Of course, the SUM formula in Excel can’t only add up a set of data. You can use it for various purposes, such as adding only certain rows, adding the largest number, or using AutoSUM.

Let’s learn more…

SUM Formula Writing Rules in Excel

Before we look at a simple example of using the SUM formula in Excel, it’s a good idea for you to understand the rules for writing the SUM formula and how to use it.

By default, here are the rules for writing the SUM formula in Excel:

SUM Formula Writing Rules in Excel:
=SUM(number1,[number2], …)

It means:
Calculate the SUM on number1, [number2], and the next data

1. number1 (required): is the first number/group of numbers added up. It can be constants, cells, ranges, named ranges, table structures, or results of a function, either in the same or different worksheets/workbooks.
2. [number2] (optional), is a number / second group of numbers to be added up by the SUM formula.
3. … (optional), this argument can mean “and the next number”. That means you can add a number or a group of numbers next up to 255 arguments. So you can add arguments like this:
   • [number3],
   • [number4], until…
   • [number255].

With an argument set like the one above, you can use Excel’s SUM formula to add up numbers for a variety of purposes.

Among them, adding up all columns/rows, in different cells/ranges, multiple worksheets/workbooks, Every Nth row, SUM Largest Number, to summing a set of cells that contain error values.

However, before using the advanced SUM function in Excel, it’s good to understand how to use the SUM function in a simple example.

Please download the following practice files first.

Let’s get your guide…

Step by step to use the SUM formula in Excel

For example, to add up all the numbers in cells A1 through A10, you might use a regular addition formula (with the plus symbol “+”) like this =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10.

But wait… that formula is too long and makes your work slower.

Now let’s see how to use the SUM formula in Excel to speed up your work in these 3 steps:

1. Type function =SUM( in cell A12.
2. Select all cells to be added. In this example, cells A1 through A10. You can do this by click-hold on cell A1, then dragging it to cell A10.
3. Type the closing parenthesis, then press Enter. Results:

You can see, that Excel calculates the sum of all the numbers correctly, which is 2+4+6+8+10+12+14+16+18+20= 110.

So, you don’t need to type all the numbers/cells one by one into the SUM formula. Just use a data range, like A1:A10.

I think the SUM formula is more effective when you want to add up a lot of numbers at once.

Important! If you don’t know the difference between rows, columns, cells, and ranges in Excel, please visit the Excel Range Guide.

BTW, maybe a little explanation about the meaning of the SUM function above can help you understand this guide.

Excel SUM Formula for a range of data:
=SUM(A1:A10)

It means:
Calculate the SUM of data from cell A1 to A10

In addition to summing numbers across multiple rows, you can also sum numbers across multiple columns.

For example, to add up all the numbers in cells A1, B1, and C1 at once, use the =SUM(A1:C1) formula. See this example:

Remember, these three cells are also called the range A1:C1. It can be done because the cells are next to each other.

Now, let’s learn some ways to use the following advanced SUM formula in Excel:

#1 SUM of all Rows/Columns in Excel

The SUM function in Excel can also add up all data in one column or one row. I think, it can speed up your work, especially when you work with big data.

Use row numbers or column letters in the SUM formula to calculate the sum of a range of cells in Excel.

So, You can use =SUM(A:A) to add all values in column A and =SUM(1:1) to add values in row 1. See results:

Let’s explain the meaning of the SUM formula above!

Excel SUM formula for entire row:
=SUM(A:A)

It means:
Calculate the SUM of data from all rows in column A

Note: If you’ve data that uses table format in Excel, please use the Total ROW Option to calculate the SUM of all the rows in each column. Like the following picture:

Easy isn’t it? Let’s check the next example.

#2 SUM Formula for Non-Contiguous Cells/Range

If you have data from two different (non-contiguous) cells or data ranges, then use the second argument in the SUM formula.

For example, I have data in the ranges A2:A11 and C2:C7. To add up all the values in the 2 data ranges, please follow these steps:

1. Type function =SUM( in cell F2.
2. Select cells A2:A11 for the first data range.
3. Type a comma , to separate the SUM formula arguments.
4. Select cells C2:C7 for the second data range.
5. Type a closing parenthesis ) to end the formula. Then press Enter. Here are the results:

You’ll find this task quite easy, won’t you?

Let’s expand your understanding of the SUM Formula used above!

Excel SUM formula for non-contiguous cells/ranges:
=SUM(A2:A11,C2:C7)

It means:
Calculate the SUM of data from cell A2 to A11 and cell C2 to C7

Even if you have more non-contiguous cells, you can still add up to 256 SUM formula arguments.

For example, the formula =SUM(A2,C3,F1) will only add up the values in cells A2+A3+F1= 2+58+18= 78.

You can also use different ranges and cells at once in a SUM formula.

For example, the formula =SUM(A2:A11,C3,F1) will add up all the values in the range A2 to A11, then the result will be added to the values in cells C3 and F1.

#3 Using SUM Across Multiple Worksheets

You can also add data from two or more different sheets by adding cells or ranges on other sheets to the SUM formula argument.

For example, I have 2 sheets named – “Data 1” and “Data 2”. The data is arranged in column B of each sheet as shown in the following image:

Note: I’ve data in the same range on both sheets. Apart from that, the rules for using formulas on different sheets also have slightly different rules. You can’t use the =SUM(B2:B11,B2:B11) formula, this will only add the same two ranges 2×.

So, to calculate data using the SUM formula from two or more different sheets in Excel, please follow these steps:

1. On Sheet “Data 1”, type the formula =SUM(.
2. Select Range B2:B11.
3. Type the formula split operator ,.
4. Click Sheet “Data 2”. then select the range B2:B11.
5. Type the closing bracket ), then press Enter. See the results:

Then, pay attention to the meaning of the arguments in the following SUM formula:

Excel SUM Formula Across Multiple Worksheets:
=SUM(B2:B11,‘Data 2’!B2:B11)

It means:
Calculate the SUM of data from Cell B2 to B11 and Cell B2 to B11 in Sheet Data 2

Easy isn’t it?

Important! To make your work easier in using formulas on different sheets, use the View Multiple Worksheet feature. This will help you view 2 different sheets at a time.

#4 Excel SUM Formula for Every Nth Row

This example uses a combination of the SUM, MOD, and ROW functions. My goal is to add up all the data in each 2nd row.

Note: I have data from Range C1:C10. Since I want to add up the data for each 2nd row, Excel will add up the data in cells C2+C4+C6+C8+C10. How to do? Pay attention to the following steps:

1. Type the function =SUM( in cell C11, then select the data range C1:C10 as in the previous method.
2. Type the asterisk *, then type the opening parentheses (. So the SUM function becomes =SUM(C1:C10*(
3. Type the MOD and ROW formulas as criteria for determining which rows will be calculated. In this example type as follows: MOD(ROW(C1:C10),2)=0)).
4. Press the Ctrl + Shift + Enter keys on the keyboard simultaneously. Here are the results:

Excel only adds up the numbers in every second row, namely C2+C4+C6+C8+C10 or 3+9+10+31+8=61.

Please look at the Formula Bar, Excel automatically encloses the formula with curly brackets { }. So the formula becomes {=SUM(C1:C10*(MOD(ROW(C1:C10),2)=0))}. This is often also called an Array Formula.

Note: Don’t type curly brackets manually. Just press Ctrl + Shift + Enter simultaneously.

Let me explain the formula:

SUM Formula Every Nth Row:
=SUM( C1:C10*(MOD(ROW(C1:C10),2)=0))

It means:
Calculate the SUM from cell C1 to C10 with the condition that the data is taken from cell C1 to C10 for every 2^nd Row, while the other rows are considered = 0

You can change number 2 (in red) in the formula according to your needs.

#5 Excel SUM Formula for Largest Numbers

This example aims to add up the largest number from a set of data. For example, I want to add up the 4 largest numbers from the data range C1:C10.

For example, I want to add up the 4 largest numbers from the data range C1:C10. If the data is sorted from smallest to largest as follows: 14, 3, 18, 9, 11, 10, 6, 31, 10, 8.

This data shows that the 4 largest numbers are 11, 14, 18, and 31. So, I want to add up these numbers in Excel. How to?

You will combine the SUM and LARGE formulas. Please follow the following steps:

1. Type the function =SUM( in cell C11.
2. Enter the LARGE Function to filter the largest data so that the formula becomes =SUM(LARGE(.
3. Select the data range C1:C10, then type a comma , as the formula separator operator.
4. Enter the criteria, namely the 4 largest numbers. In the LARGE Function, if more than 1 criterion is used, the criteria must be enclosed in curly braces { }. So it becomes {1,2,3,4}.
5. Type close brackets 2x. Then press Enter. Here are the results:

Excel gives the result 74, which is the sum of the 4 largest numbers 31 +18+14+11 = 74.

If you change the criteria in the LARGE Formula to {1,2}, then Excel will only add 31 and 18. Here I explain the formula:

SUM Formula for Largest Numbers:
=SUM(LARGE(C1:C10,{1,2,3,4}))

It means:
Calculate The SUM of the Largest Values in cell C1 to C10 which are the 1st, 2nd, 3rd, and 4th Largest Values

#6 SUM Range with Error Message

For example, I’ve data in the range C1:C10. Meanwhile, some cells contain Excel error messages such as cell C1: #REF!, cell C6 = #DIV/0!, and cell C9 = #NAME?.

If you run into a situation like this, you can’t just use the SUM formula. Excel will automatically provide error message results such as #REF!.

To overcome this problem, please use a combination of the SUM and IFERROR formulas as follows:

1. Type the function =SUM( in cell C11.
2. Input the formula IFERROR(.
3. Select data range C1:C10.
4. Type a comma then enter the value 0 and 2x closing brackets ,0)). The value 0 aims to change the error message to 0.
5. Press Ctrl + Shift + Enter simultaneously. Here are the results:

The IFERROR function will change the Excel error message to zero. So Excel will add up the data as follows: 0 + 3 + 18 + 9 + 11 + 0 + 6 + 31 + 0 + 8 = 86.

Here is the meaning of the formula:

SUM Range with Error Message:
=SUM(IFERROR(C1:C10,0))

It means:
Calculate the SUM. If there is an error message in cell C1 to C10, then change the error value to 0

How easy is it for you?

Next, let’s learn about a much more interesting SUM feature, namely AutoSUM.

#7 Automatically Add Using the AutoSUM Feature

Apart from typing the SUM function manually, you can also calculate the amount of data using the AutoSUM feature. This can save you a lot of time.

Still using the same example, please follow the steps for adding using Excel’s AutoSUM feature below:

1. Click cell C12 as the place for the sum results.
2. Click the Home Tab.
3. In the editing group, click AutoSUM. Automatically, Excel will fill in the SUM formula argument with the data in the row above (as shown), because the data is arranged vertically. If the data is arranged horizontally, then Excel will fill in the SUM function argument with the data in the left side column.
4. Press Enter to continue. Here are the results:

Note: The AutoSUM feature in Excel will add up all the data in the column above or the row to the left.

Why are SUM Formula and AutoSUM not Working?

If the SUM formula and AutoSUM feature in Excel not working, there may be a problem with your data.

If there are errors in your data, the SUM formula and Excel’s AutoSUM feature will return zeros or an Error Message.

Look at the following image:

The SUM and AutoSUM formulas in cells B12 and B13 return zero values. If you experience problems like this, please change the Number Format on all your data.

Because the SUM and AutoSUM formulas can only add up data in the form of numbers.

Meanwhile, the SUM and AutoSUM functions in cells D12 and B13 return an Error Message.

You can overcome this problem by eliminating all errors in the data or using a combination of the SUM and IFERROR formulas as in the example above.