Sum Formula in Excel

The Addition is one of the familiar concepts in Mathematics, as it is essential in everyday life. It involves combining two or more values, and the obtained result is called Sum. The adding values are called Addend. For example,

1+4=5.

Here 1 and 4 are called Addend

5 is called Sum, which is represented after the equal sign.

It is represented by the symbol "+."

Adding up data is one of the most popular activities in the Excel world. Because of this reason, Microsoft Excel is equipped with several built-in functions such as SUM, SUMIF, and SUMIFS. The built-in SUM function in Excel not only helps to sum up a range of cells, a row, column, or non-contiguous cells but can also be combined with other Excel functions to make advanced SUM formulas.

The tutorial, we will cover the various means to calculate the Sum Range, will determine the Sum of an entire column, the Sum of Non-contiguous cells, working with Excel AutoSum, steps to sum every Nth Row, how to calculate the Sum of the largest numbers, Sum Range with errors and many more.

Applications of Addition

Some of the real-time applications of Addition are as follows:

Shopping

Day-to-day life activities like Shopping involve the money-transferring process. The money is transferred between the shop owner and the customer in the shopping process. For perfect calculation, the concept of Addition is needed here.

Computers and Calculators

Digital computers and Calculators perform the concept of Addition quickly and efficiently. In this digital world, digital computers and calculators are needed to perform extensive calculations.

Ordering

While ordering things directly or online, the concept of Addition is required to count the items or stock availability.

Real Numbers

Numbers play a significant role in everything. For example, counting the stars includes Addition while traveling for each kilometer, and the numbers are added while buying a heap of books, the numbers are added, etc.

Arithmetic

The arithmetic operation involves everything, such as counting chocolate, money savings added every month, etc. Most of the activities involve arithmetic operations.

Building a Family

The Members of the family will expand based on specific times. For example, a baby is born, next level to children, next level to adults, and next level to a young Boy. After marriage, his family consists of himself, his wife, and two children. In this process, the concept of Addition takes place as a new member is added at a particular time.

In literature and daily living

While studying the book, the count of page numbers is added every time. While cooking, the utensils are counted.

Ballet, Opera, etc

In ballet or group dance, only a select count of people will dance, such as Ballet dance. The perfect number of people involves the concept of Addition.

Services

Daily, we come across various money-transferring processes such as electricity bills, doctor bills, gardening, parlor, etc. This service involves the concept of Addition.

Birthday, calendars, and events

The various happy events such as birthdays, weddings, housewarming functions, and starting a new business happen on a particular day and date. Every year, these dates are added with one, which involves the Addition concept.

Sum Range

This is one of the most commonly used operation in Excel. For most of the cases, users calculate the Sum Range using the built-in SUM function in Excel.

What is Sum Function?

The SUM function in Excel returns the sum of the specified range of cells. In any combination, these cells can contain numbers, cell references, ranges, arrays, and constants. The SUM function can accept up to 255 separate arguments.

Syntax

Parameters

number1 (required) - This parameter represents first value to sum.

number2 (optional) - This parameter represents second value to sum.

number3 (optional) - This parameter represents third value to sum.

Return value

The SUM function returns the sum of the given values.

Example: Calculate the Sum Range for the given group of cells.

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

1. Select a cell where you will want the output of the Sum Range.
   • If you want to calculate the sum of a column, select the cell below the last value in the same column.
   • If you want to calculate the sum of a row, select the cell to the right of the last value in the same row.
2. Start your formula with equal to '=' and mention the SUM. In the arguments section, specify the range of cells for which you want to calculate the SUM. You can directly select the cells using your mouse cursor.
   Formula becomes: = SUM (A4:A10)
   
3. Press the enter key once done. Excel will throw the following result:
   

Sum Entire Column

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

Example: Calculate the Sum of an entire column.

Following are the rules to quickly calculate the SUM of an entire column:

1. Select a cell where you will want the output of the Sum Range.
2. Start your formula with equal to '=' and mention the SUM. In the arguments section, specify the column name for which you want to calculate the SUM. You can directly select the column using your mouse cursor.
   Formula becomes: = SUM(A:A)
   
3. Press the enter key once done. As a result, Excel will calculate the sum of entire column and fetch the following result:
   

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.

How to sum only filtered cells in Excel?

Sometimes the data requirement is to add only the selective data called filtered cells. Excel provided the option to sum only filtered cells. The steps to be followed are:

1. Enter the data in the table range, namely A1:B11
   
2. In the data, only the total values of Mangoes need to be counted. Hence filtering option is applied. Select any data in the cell, and choose the filter option from the data tab.
   
3. Once the filter option is clicked, the arrows will appear in the column header.
   
4. Now click on the appropriate header to narrow down the data. Here the Product header is chosen. There are several options; in that, check "Mango" and uncheck all other options. Click Ok.
   
5. Now select the range with the numbers to add and click the option "AutoSum" in the Home tab.
   
6. The count of the Mango will display in the cell.
   

The formula displays as =SUBTOTAL (9, B4:B11), which counts the value of the Mango.

Sum Non-contiguous Cells

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

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

Given-below are the steps to compute the SUM of the non-contiguous cells:

1. Select a cell where you will want the sum output of the non-contiguous cells.
2. Start your formula with equal to '=' and mention the SUM. In the arguments section, you have to specify the individual cells.
   Here's a little trick.
   Press the CTRL button and using your mouse cursor click on the non-contiguous cells separately.
   Formula becomes: =SUM(A2,A4,B3,C3,C4,B5,D5)
   
3. Press the enter key once done. As a result, Excel will calculate the sum of the non-contiguous cells and will return the following output:
   

Note: Instead, you inserting the above formula, you can also calculate the sum with = A2 + A4 + B3 + C3 + C4 + B5 + D5. You will notice both the formula produces the exact same output!

SUM using AutoSum

Why manually enter the SUM formula and specify the range when Microsoft Excel can do the math for you. Whenever you want to calculate the sum of the range of cells, an entire column, row, or several adjacent columns or rows, you can rely upon the Excel AutoSum tool that automatically makes an accurate SUM formula. When you click AutoSum, Excel automatically create the SUM formula (using the inbuilt SUM function) to sum the specified cells.

To use Excel's AutoSum tool, follow the below-given 3 steps:

STEP 1: Select the cell

Select a cell where you will want the sum output of the AutoSum

• If you want to calculate the sum of a column, select the cell below the last value in the same column.
• If you want to calculate the sum of a row, select the cell to the right of the last value in the same row.

STEP 2: Click on AutoSum

Go to the Home tab. Click on the Editing group > AutoSum:

OR

Click on the Formulas tab. Go to the Function Library group > AutoSum:

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

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

STEP 3: Press the Enter button to complete the process.

Now, you can see the calculated total in the cell, and the SUM formula in the formula bar:

NOTE: If you 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, 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 saviour since nothing can prevent you from creating your customised formulas!

To find the Sum of every Nth Row we can use the combination of the Excel functions like SUM, MOD and ROW. Given-below are the steps to compute the SUM of every Nth row:

1. Select a cell where you will want the sum output of every Nth row.
2. Start your formula with equal to '=' and mention the SUM. To calculate the sum of every 3rd row, we will create a formula using the combination of 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 not to type them manually. Enter an array formula to the above formula by pressing the keyboard shortcut CTRL + SHIFT + ENTER.
3. Press the enter key once done. As a result, Excel will calculate the sum of the non-contiguous cells and will return the following output:
   
4. If you want to calculate the sum of every 5th row, simply change the 3 to 4 to sum every 5th row.

Sum Largest Numbers

If you store business data in Excel, such as sales data for a specific region, the weight of certain machinery parts, and expenses of a month, you may need a quick way to sum up, the largest numbers to prices or amounts.

Below are very simple and straightforward steps to calculate the SUM of the largest numbers in a range using the inbuilt SUM and LARGE. Even if you are a beginner in Excel, you will hardly have any problem understanding the following examples.

1. Select a cell where you will want the output of the Sum of largest numbers Range.
2. Start your formula with equal to '=' and mention the SUM. In the arguments section, specify the LARGE function, inside the parameters mention range of cells for which you 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.
   Formula becomes: =SUM(LARGE(A2:A13,{1,2,3,4}))
   
3. Press the enter key once done. Excel will return the following result:
   
4. If you want to find the sum of 5 largest number simply change {1,2,3,4} to {1,2,3,4,5}.
   
   Note: With a little modification you can also calculate second largest number using the formula =LARGE(A1:A11,2).

Sum Range with Errors

Formula and Error run hand in hand. Similarly, while working with the sum formula, users often end up with Excel errors.

In the below image, as you can see the SUM formula returns the #NAME? error since we have incorporated text within the formula.

Using error control methods can check the errors. A combination of the inbuilt SUM and IFERROR functions is used to sum a range with errors. You can also use the AGGREGATE function in Excel to sum a range with errors.

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

1. Select a cell where you will want the output of the Sum Range.
2. Start your formula with equal to '=' and mention the IFERROR function to check if there exists any error. In the value arguments, we will specify the SUM function and the values. In the value_if_error argument of the IFERROR function, we will specify the text you want to display instead of the error. You can directly select the cells using your mouse cursor.
   Formula becomes: =IFERROR(SUM(34,45,reema),"Numbers not valid")
   
   NOTE: You can also enter customised message in the IFERROR function.
3. Press the enter key once done. Instead of throwing the error, Excel will return the IFERROR text. You will have the following result:
   
   NOTE: The Excel SUM function automatically ignores the text values.

Converting the data to a Table and adding the values

The user can convert data to the table, adding the specified columns. This process helps to simplify the totalling of the rows and columns and performs various operations.

The steps to be followed to convert the data to a table are as follows:

1. Enter the data in the worksheet, namely A1:B6
   
2. Press the shortcut key, called Ctrl+T, to format the selective data.
   
3. The data is converted to a table. In the design tab, tick the checkbox containing "Total Row."
   
4. While clicking this option, the new row will be added at the end of the total, which displays the Sum of the total. Click the cell containing the total, where a small header area will display. To check whether the displayed result is Sum, click the header area. There are several options, and in that choose the "Sum ."By using this option, it displays the" sum" present in each column.
   

Similarly, one can choose various options like Average, Count, Max, Min, StdDev, and Var based on various functions.

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