Which function totals all the values in a range of cells and returns the result?
Return valueThe sum of values supplied. Show
Usage notesThe SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments. The SUM function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. All numbers in the arguments provided are summed. The SUM function automatically ignores empty cells and text values, which makes SUM useful for summing cells that may contain text values. The SUM function will sum hardcoded values and numbers that result from formulas. If you need to sum a range and ignore existing subtotals, see the SUBTOTAL function. ExamplesTypically, the SUM function is used with ranges. For example:
Values in all arguments are summed together, so the following formulas are equivalent:
In the example shown, the formula in D12 is:
References do not need to be next to one another. For example:
Sum with text valuesThe SUM function automatically ignores text values without returning an error. This can be useful in situations like this, where the first formula would otherwise throw an error. Keyboard shortcutExcel provides a keyboard shortcut to automatically sum a range of cells above. You can see a demonstration in this video. Notes
For example, cells B1, B2, and B3 contain 20, 44, and 67 respectively. The formula “=SUM(B1:B3)” adds the numbers of the cells B1 to B3. It returns 131. The SUM formula automatically updates with the insertion or deletion of a value. It also includes the changes made to an existing cell range. Moreover, the function ignores the empty cells and text values. The Syntax of the SUM Excel FunctionThe syntax of the function is shown in the following image: The function accepts the following arguments:
The “number1” argument is required while the subsequent numbers (“number 2”, “number 3”, etc.) are optional. The Procedure to Enter the SUM Function in ExcelTo enter the SUM function manually, type “=SUM” followed by the arguments. The alternative steps to enter the SUM excel function are listed as follows:
2. From the drop-down menu that opens, select the SUM option. 3. In the “function arguments” dialog box, enter the arguments of the SUM function. Click “Ok” to obtain the output. The AutoSum Option in ExcelThe AutoSum option is the fastest way to add numbers in a range of cells. It automatically enters the SUM formula in the selected cell. Let us work on an example to understand the working of the AutoSum option. We want to sum the list of values in A2:A7, shown in the succeeding image. The steps to use the AutoSum command are listed as follows:
How to Use the SUM Function in Excel?Let us consider a few examples to understand the usage of the SUM function. The examples #1 to #5 show an image containing a list of numeric values. Example #1We want to sum the cells A2 and A3 shown in the succeeding image. Apply the formula “=SUM(A2, A3).” It returns 20.7 in cell C2. Example #2We want to sum the cells A3, A5, and the number 45 shown in the succeeding image. Apply the formula “=SUM (A3, A5, 45).” It returns 58.8 in cell C2. Example #3We want to sum the cells A2, A3, A4, A5, and A6 shown in the succeeding image. Apply the formula “=SUM (A2:A6).” It returns 135.4 in cell C2. Example #4We want to sum the cells A2, A3, A5, and A6 shown in the succeeding image. Apply the formula “=SUM (A2:A3, A5:A6).” It returns 35.4 in cell C2. Example #5We want to sum the cells A2, A3, A5, A6, and the number 500 shown in the succeeding image. Apply the formula “=SUM (A2:A3, A5:A6, 500).” It returns 535.4 in cell C2. The Usage of the SUM Excel FunctionThe rules governing the usage of the function are listed as follows:
Note: The SUM excel function returns the “#VALUE!” error if the criterion supplied is a text string longer than 255 characters. The Limitations of the SUM Function in ExcelThe drawbacks of the function are listed as follows:
The Nesting of the SUM Excel FunctionThe built-in formulas of Excel can be expanded by nesting one or more functions inside another function. This permits multiple calculations to take place in a single cell of the worksheet. The nested function acts as an argument of the main or the outermost function. Excel calculates the innermost function first and then moves outwards. For example, the following formula shows the SUM function nested within the ROUND functionThe ROUNDUP excel function calculates the rounded value of the number to the upward side or the higher side. In other words, it rounds the number away from zero. Being an inbuilt function of Excel, it accepts two arguments–the “number” and the “num_of _digits.” For example, “=ROUNDUP(0.40,1)” returns 0.4. read more: For the given formula, the output is calculated as follows:
With Microsoft Excel 2007, nested functions up to 64 levels are permitted. Prior to this version, one could nest functions only till 7 levels. Frequently Asked Questions1. Define the SUM function of Excel. The SUM function helps add the numerical values. These values can be supplied to the function as numbers, cell references, or ranges. The SUM function is used when there is a need to find the total of specified cells. The syntax of the SUM excel function is stated as follows:
In the SUM function, the range to be summed can be provided, which is easier than typing the cell references one by one. The AutoSum option provided in the Home or Formulas tab of Excel is the simplest way to sum two numbers. Note: The numeric value provided as an argument can be either positive or negative. 2. How to sum the values of filtered data in Excel? To add the values of filtered data, use the SUBTOTAL function. The syntax of the function is stated as follows: “SUBTOTAL(function_num,ref1,[ref2],…)” The “function_num” is a number ranging from 1 to 11 or 101 to 111. It indicates the function to be used for the SUBTOTALThe SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined range.read more. The functions used can be AVERAGE, MAX, MIN, COUNT, STDEV, SUM, and so on. The “ref1” and “ref2” are the cells or ranges to be added. The “function_num” and “ref1” arguments are mandatory. The “function_num” 109 is used for adding the visible cells of filtered data. Let us consider an example. • The sales revenue generated by A
and B of team X are: • The sales revenue generated by C and D of team Y are: We filter only team X rows and apply the formula “SUBTOTAL(109,C2:C3).” It returns $4,802. Note: Alternatively, the AutoSum property can be used to sum the filtered cells. 3. State the benefits of using the SUM function in Excel. The benefits of using the SUM function are listed as follows: SUM Function in Excel VideoRecommended ArticlesThis has been a guide to the SUM function in Excel. Here we discuss how to use Sum Formula along with step by step examples and FAQs. You can download the Excel template from the website. Take a look at these useful functions of Excel–
Which function totals all the cells in the range?You can use a simple formula to sum numbers in a range (a group of cells), but the SUM function is easier to use when you're working with more than a few numbers. For example =SUM(A2:A6) is less likely to have typing errors than =A2+A3+A4+A5+A6.
Which function adds a range of cells and returns results?The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the values in cells A2:10.
What function returns the number of cells in a range?Excel contains several functions to help you count the number of cells in a range that are blank or contain certain types of data. Select the cell where you want the result to appear. COUNT: To count cells that contain numbers. COUNTBLANK: To count cells that are blank.
Which formula in Excel takes all the values in a range and totals their values?SUM: This function adds all the values of the cells in the argument.
|