Sum Formula in ExcelThe 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 AdditionSome of the real-time applications of Addition are as follows: ShoppingDay-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 CalculatorsDigital 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. OrderingWhile ordering things directly or online, the concept of Addition is required to count the items or stock availability. Real NumbersNumbers 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. ArithmeticThe arithmetic operation involves everything, such as counting chocolate, money savings added every month, etc. Most of the activities involve arithmetic operations. Building a FamilyThe 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 livingWhile studying the book, the count of page numbers is added every time. While cooking, the utensils are counted. Ballet, Opera, etcIn 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. ServicesDaily, 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 eventsThe 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 RangeThis 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
=SUM (number1, [number2], [number3], ...)
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:
Sum Entire ColumnThe 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:
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:
The formula displays as =SUBTOTAL (9, B4:B11), which counts the value of the Mango. Sum Non-contiguous CellsThe 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:
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 AutoSumWhy 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
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 RowIn 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:
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. Sum Largest NumbersIf 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.
Sum Range with ErrorsFormula 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:
Converting the data to a Table and adding the valuesThe 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:
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.
Next TopicExcel #SPILL! error
|