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 builtin functions like SUM, SUMIF, and SUMIFS. Moreover, the builtin SUM function in Microsoft Excel helps us sum up a range of cells, a row, column, or noncontiguous. 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 Noncontiguous 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 ExcelIt is considered one of the most commonly used operations in Microsoft Excel. And for most cases, users calculate the Sum Range using the builtin 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
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.
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 ExcelThe 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 Noncontiguous Cells'NonContiguous' 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 noncontiguous cells in our Excel spreadsheet. Example: To calculate the Sum of the noncontiguous cells that contain numeric values. Given below are the following steps that can be used to compute the SUM of the noncontiguous cells: Step 1: First, we need to select a cell where we want the sum output of the noncontiguous 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 noncontiguous 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 "noncontiguous 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 AutoSumWhy 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 belowgiven 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.
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. 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 RowIn 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 builtin 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 noncontiguous 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 NumbersSuppose 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 builtin 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 ErrorsFormula 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.
Next TopicExcel #SPILL! error
