Purpose
Add numbers together
Return value
The sum of values supplied.
Syntax
=SUM(number1,[number2],[number3],...)- number1 - The first value to sum.
- number2 - [optional] The second value to sum.
- number3 - [optional] The third value to sum.
How to use
The 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.
Examples
Typically, the SUM function is used with ranges. For example:
=SUM(A1:A9) // sum 9 cells in A1:A9
=SUM(A1:F1) // sum 6 cells in A1:F1
=SUM(A1:A100) // sum 100 cells in A1:A100
Values in all arguments are summed together, so the following formulas are equivalent:
=SUM(A1:A5)
=SUM(A1,A2,A3,A4,A5)
=SUM(A1:A3,A4:A5)
In the example shown, the formula in D12 is:
=SUM(D6:D10) // returns 9.05
References do not need to be next to one another. For example:
=SUM(A1,F13,E100)
Sum with text values
The 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 shortcut
Excel provides a keyboard shortcut to automatically sum a range of cells above. You can see a demonstration in this video.
Notes
- SUM automatically ignores empty cells and cells with text values.
- If arguments contain errors, SUM will return an error.
- The AGGREGATE function can sum while ignoring errors.
- SUM can handle up to 255 total arguments.
- Arguments can be supplied as constants, ranges, named ranges, or cell references.
SUM function examples
Running total in Table
At the core, this formula has a simple pattern like this: =SUM(first:current) Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column. To get the a reference to the first cell, we use INDEX like this: INDEX([Total],1) Here,...
Sum multiple tables
This formula uses structured references to refer to the "Amount" column in each table. The structured references in this formula resolve to normal references like this: =SUM(Table1[Amount],Table2[Amount]) =SUM(C7:C11,F7:F13) =1495.5 When rows or columns are added or removed from either table, the...
Sum top n values with criteria
In this example, the goal is to sum the largest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three largest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps: Apply criteria to select specific...
Sum if multiple columns
In this example, the goal is to calculate a sum for any given group ("A", "B", or "C") across all three months of data in the range C5:E16. In other words, we want to perform a "sum if" with a data range that contains three columns. For convenience only, data (C5:E16) and group (B5:B16) are named...
Sum race time splits
Excel times are fractional numbers . This means you can add times together with the SUM function to get total durations. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below. Enter times in the correct format You must...
Sum bottom n values
In this example, the goal is to sum the smallest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n smallest values from the data set and (2) sum the extracted values. This problem can be solved...
Odometer gas mileage log
Note: this example assumes that fuel is added to capacity at each gas stop, in order to calculate miles per gallon (MPG) based on the miles driven and fuel used since the last stop. In addition, this example keeps all data in an Excel Table called "data" to illustrate how Tables can make some...
Sum last n columns
In this example, the goal is to sum the last n columns in a set of data, where n is a variable that can be changed at any time. In the latest version of Excel, the easiest way to solve this problem is with the TAKE function . In older versions of Excel you can use the OFFSET function , as explained...
If cell contains this or that
The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself to test for a substring like "abc" or "xyz" that might...
Lookup and sum column
The core of this formula uses the INDEX and MATCH function in a special way to return a full column instead of a single value. Working from the inside out, the MATCH function is used to find the correct column number for the fruit in I6: MATCH(I6,C4:F4,0) MATCH return 2 inside the INDEX function as...
Weighted average
In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean ) is an average where some values are more...
Calculate running total
In this example, the goal is to calculate a running total in column D of the worksheet as shown. A running total, or cumulative sum, is a set of partial sums that changes as more data is collected. Each calculation represents the sum of values up to that point. In this example, each calculation...
Count unique text values with criteria
This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in the data: MATCH(B5:B11,B5:B11,0) The result from MATCH is an array like this: {1;...
Sum bottom n values with criteria
In this example, the goal is to sum the smallest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three smallest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps: Apply criteria to select specific...
Sum first n rows
In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts by position , starting with cell C5...
SUM function videos
How to set up a running total in a table
In this video, we'll look at how to set up a running total in an Excel table. Setting up a running total in an Excel table is a little tricky because it's not obvious how to use structured references. This is because structured references provide a notation for current row, but not for first row in...
Excel formula error codes
In this video, we'll take a look at the error codes that Excel generates when there's something wrong with a formula. There are 8 error codes that you're likely to run into at some point as you work with Excel's formulas. First, we have the divide by zero error. You'll see this when a formula tries...
How to use relative references - example 2
In this lesson we'll take a look at another common use case for relative references—creating row and column totals in a table. These kinds of totals often use formulas that are identical except for the cell references. Let's take a look. Here we have a table that shows the number of widgets sold...
What is an Excel Table
In this video, we'll introduce the idea of an Excel Table. So, what is an Excel Table? An Excel table is a rectangular range of data that has been defined and named in a particular way. To illustrate, here I have two rectangular ranges of data. Both ranges contain exactly the same data but neither...
Create a dynamic reference to a named range
In this video we'll look at how to create a dynamic reference to a named range using the INDIRECT function . Let's take a look. Here we have a simple table that summarizes sales by salesperson over a four-month period. What we're going to do is use the INDIRECT function to make a dynamic reference...
How Excel Table ranges work
In this video, we'll take a closer look at how table ranges work. One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest...
How to use the SUM function
The SUM function is a simple function that adds up numbers. Let's take a look. The SUM function sums numeric values. For example, if I enter the formula =SUM(B7:B11) Excel will return "50," the total of all numbers in that range. SUM simply ignores blank cells. The formula =SUM(D7:E9) will return "...
Examples of flagged errors in formulas
In this video, we'll look at a few examples of the kind of errors that Excel will flag on a worksheet and the rules that control these errors. First, to recap, the rules that govern the errors that Excel flags are located at options > formulas > error checking rules Each rule can be disabled...
How to use the SUMIFS function
In this video, we'll look at how to use the SUMIFS function to sum cells that meet multiple criteria. Let's take a look. SUMIFS has three required arguments: sum_range, criteria_range1, and criteria1 . After that, you can enter additional range and criteria pairs to add additional conditions. In...
Excel Table Options
In this video, we'll look at the options available for Excel Tables. Excel provides four settings for Tables you should be aware of. The first option controls whether a table range will automatically adjust as data changes. By default, this option is enabled, since this feature is a key benefit of...
Related functions
SUMIF Function
The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching....
SUMIFS Function
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria. To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
