What is meant by Sum Formula in Microsoft Excel?

Adding up data is one of the most popular activities in the Microsoft Excel world. Because of this reason, Microsoft Excel is equipped with several built-in functions like SUM, SUMIF, and SUMIFS.

Moreover, the built-in SUM function in Microsoft Excel helps us sum up a range of cells, a row, column, or non-contiguous. It can also be combined with other Microsoft Excel functions to make advanced SUM formulas.

This tutorial will cover the various means to calculate the Sum Range, it will determine the Sum of an entire column, and the Sum of the Non-contiguous cells, working with Excel AutoSum, steps to sum every Nth Row, and we will also learn how one can easily calculate out the 'Sum of the largest numbers,' 'Sum Range with errors' and many more.

Sum Range in Microsoft Excel

It is considered one of the most commonly used operations in Microsoft Excel. And for most cases, users calculate the Sum Range using the built-in SUM function in Excel efficiently.

What is meant by Sum Function in Microsoft Excel?

Now the SUM function in Microsoft Excel usually returns the Sum of the specified range of cells. These cells can contain numbers, cell references, ranges, arrays, and constants in any combination. The SUM function can accept up to 255 separate arguments as well.

Syntax

=SUM (number1, [number2], [number3], ...)

Parameters

1. number1 (required) - This parameter also represents the first value to sum.
2. number2 (optional) - This parameter primarily represents the second value to the Sum.
3. number3 (optional) - This particular parameter represents the third value to the Sum.

Return value

The SUM function returns the Sum of the given values.

Example: To calculate the Sum Range for the given group of cells.

Following are the rules that can be used to quickly calculate out the SUM Range by just making use of the SUM function respectively:

Step 1: First, we are required to select a cell where we want the output of the Sum Range.

• If we want to calculate the Sum of a column, we must select the cell below the last value in the same column as well.
• And if we want to calculate the Sum of a row, we must select the cell to the right of the last value in the same row respectively.

Step 2: After that, we will start our formula with an equal to '=' and mention the SUM. In the arguments section, we need to specify the range of cells we want to calculate the SUM. And we can directly select the cells by using our mouse cursor.

Formula becomes: = SUM (A4:A10)

Step 3: Now, just after that, we need to press the "enter key" from our keyboard once done. Excel will throw the following result respectively:

Some of the Entire Column in Microsoft Excel

The SUM function can also be used to calculate the Sum of an entire column.

Example: To calculate the Sum of an entire column.

Following are the rules or the step that can be used to calculate the SUM of an entire column quickly:

Step 1: First, we need to select a cell where we want the output of the Sum Range.

Step 2: After that, we start our formula with an equal to '=' and mention the SUM. And in the arguments section, we need to specify the column name for which we want to calculate the SUM. And we can directly select the column by using our mouse cursor respectively.

Formula becomes: = SUM(A:A)

Step 3: Press out the "enter key" once done. And as a result, Microsoft Excel will calculate the Sum of the entire column and fetch the following result respectively:

NOTE: With some little changes, you can use the above SUM function to calculate the Sum of an entire row. For example, = SUM (10:10) adds all the cell values in the 10th row.

Sum Non-contiguous Cells

'Non-Contiguous' refers to the cells not next to each other and located at different positions on our worksheet. The SUM function in Microsoft Excel can also calculate the Sum of non-contiguous cells in our Excel spreadsheet.

Example: To calculate the Sum of the non-contiguous cells that contain numeric values.

Given below are the following steps that can be used to compute the SUM of the non-contiguous cells:

Step 1: First, we need to select a cell where we want the sum output of the non-contiguous cells.

Step 2: After that, we start our formula with an equal to '=' and mention the SUM. In the arguments section, we also need to specify the individual cells.

Here's a little trick.

We need to press the "CTRL button" and, by using our mouse cursor, click on the non-contiguous cells separately.

Formula becomes: =SUM(A2,A4,B3,C3,C4,B5,D5)

Step 3: Nextly, we need to press the "enter key" from our keyboard once done. And as a result, Microsoft Excel will calculate the Sum of the "non-contiguous cells" and will return the following output respectively:

Note: Instead, we insert the above formula; we can also calculate the sum with = A2 + A4 + B3 + C3 + C4 + B5 + D5. we will notice both formulae produces the same output!

SUM using AutoSum

Why manually enter the SUM formula and specify the range when Microsoft Excel can do the math for us? Whenever we want to calculate the Sum of the range of cells, an entire column, a row, or several adjacent columns or rows, then, in that case, we can rely upon the "Excel AutoSum tool" that will automatically make an accurate SUM formula as well. When we click on the "AutoSum," Microsoft Excel will automatically create the SUM formula (by just using the inbuilt SUM function) to sum the specified cells respectively.

And to make use of Excel's AutoSum tool, we are required to follow the below-given 3 steps very carefully:

STEP 1: Select the cell

First of all, we are required to select a cell where we usually want the sum output of the AutoSum as well.

• If we want to calculate the Sum of a column, we need to select the cell below the last value in the same column.
• And if we want to calculate the Sum of a row, we need to select the cell to the right of the last value in the same row respectively.

STEP 2: We will be clicking on the AutoSum

Now in this step, we need to go to the Home tab. And then we will click on the Editing group > AutoSum:

OR

We can also click on the respective "Formulas tab." Go to the Function Library group > AutoSum:

And we will notice that a Sum formula will appear in the selected cell, and the cells which we want to add will get highlighted (A2:A13 in this example):

Though in most cases, Microsoft Excel automatically selects the accurate range of cells we want to SUM. But in some cases, there are chances we make a wrong selection, but we can correct it manually by just typing the preferred range in the formula bar or by just dragging the mouse cursor through the cells we want to sum.

STEP 3: In this step, we must press the Enter button to complete the process.
Now, we can easily see the calculated total in the cell and the SUM formula in the formula bar:

NOTE: If you are one of those Excel users who prefer working with the keyboard rather than the mouse, you can use the following Excel AutoSum keyboard shortcut to total cells:

press 'ALT' + '=' to quickly sum a column or row of numbers.

Sum Every Nth Row

In some cases, however, we are only required to sum a specific range of cells in an Excel worksheet, let's say the Sum of every 3rd row to 6 to sum every 7th row, etc. Some users may find it challenging because Microsoft Excel doesn't contain any built-in function to calculate this. But as always, Excel formulas are the savior since nothing can prevent you from creating our customized formulas!

And to find the Sum of every Nth Row, we can use a combination of Excel functions like SUM, MOD, and ROW. Given below are the steps that can be efficiently used to compute the SUM of every Nth row:

Step 1: First, we need to select a cell where we want the sum output of every Nth row.

Step 2: After that, we start our formula with an equal to '=' and mention the SUM. To calculate the Sum of every 3rd row, we will create a formula by combining SUM, MOD, and ROW functions.

Formula becomes: {=SUM (A2:A13*(MOD(ROW(A2:A13),3)=0))}

Note: In the above formula, you will notice that we have put the formula in curly braces {}. These braces indicate that it is an array formula. But always remember to type them sparingly. Enter an array formula to the above formula by pressing the keyboard shortcut CTRL + SHIFT + ENTER.

Step 3: In this step, we need to press the "enter key" from the keyboard once done. And as a result, Microsoft Excel will calculate the Sum of the non-contiguous cells and return the following output respectively.

Step 4: If we want to calculate the Sum of every 5th row, we can change the 3 to 4 to sum every 5th row.

Sum Largest Numbers

Suppose we store business data in Microsoft Excel, such as sales data for a specific region, the weight of certain machinery parts, and expenses of a month. In that case, we need a quick way, to sum up the most significant numbers to prices or amounts.

Below are elementary and straightforward steps that can be used to calculate the SUM of the largest numbers in a range using the built-in SUM and LARGE. Even if we are a beginner in Microsoft Excel, we will be fine understanding the following examples.

Step 1: First, we need to select a cell where we usually want the output of the Sum of the most extensive numbers Range.

Step 2: In this step, we will start our formula with an equal to '=' and mention the SUM. In the arguments section, we are required to specify the LARGE function; inside the parameters, mention the range of cells for which we want to calculate the SUM, and in the 'k' parameter, specify the array values {1,2,3,4} since we have to calculate the top 4 values respectively.

Formula becomes: =SUM(LARGE(A2:A13,{1,2,3,4}))

Step 3: Now, just after that, we need to press the "enter key" from our keyboard once done. And Microsoft Excel will return the following result as well:

Step 4: If we want to find the Sum of the 5 largest numbers, change {1,2,3,4} to {1,2,3,4,5}.

Note: With some modification, you can calculate the second largest number using the formula =LARGE(A1:A11,2).

Sum Range with Errors

Formula and Error run hand in hand. Similarly, users often encounter Microsoft Excel errors while using the sum formula.

And in the below image, as we can see, the SUM formula returns the Error since we have incorporated text within the formula.

Using error control methods, quickly and easily check the erCombiningion of the inbuilt SUM and IFERROR functions is used to sum a range with errors. We can use of the AGGREGATE function in Microsoft Excel to sum a range with errors.

Following are the rules to quickly calculate the SUM Range using the SUM function:

Step 1:of all, we need to select a cell where we want the output of the Sum Range.

Step 2: Start our formulation with an equal to '=' and mention the IFERROR function to check if there exist any events; we will specify the SUM function and then in the value argument values. In the value_if_error argument of the IFERROR function, we will specify the text we want to display instead of the Error. We can directly select the cells using our mouse cursor respectively.

Formula becomes: =IFERROR(SUM(34,45,reema),"Numbers not valid")

NOTE: You can also enter customized messages in the IFERROR function.

Step 3: Press the enter key once done. Instead of throwing the Error, Excel will return the IFERROR text. We will have the following result:

NOTE: The Excel SUM function automatically ignores the text values.

Eureka! Now that we have covered all the SUM topics. Go ahead and create great SUM formulas per our requirements and shine like a star among your colleagues.