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
Sum every nth row
In this example, the goal is to sum every nth value in a range of data, as seen in the worksheet above. For example, if n =2, we want to sum every second value (every other value), if n =3, we want to sum every third value, and so on. All data is in the range B5:B16 and n is entered into cell F5 as...
Count visible columns
There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function , but SUBTOTAL only works with vertical ranges. As a result, the approach described in this example is a workaround based on a helper formula that must be entered in a range that...
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 time
Dates and times are just numbers in Excel, so you can use them in any normal math operation. However, by default, Excel will only display hours and minutes up to 24 hours. This means you might seem to "lose time" if you are adding up time that is more than 1 day. In this example, the goal is to sum...
LAMBDA contains one of many
Excel does not provide a dedicated "contains" function, but you can create a custom function to test if a cell contains one or many strings with the LAMBDA function . LAMBDA functions do not require VBA, but are only available in Excel 365 . The first step in creating a custom LAMBDA function is to...
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...
Count unique numeric values in a range
Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you'll need to use a more complicated formula . The Excel FREQUENCY...
Sum top n values
In this example, the goal is to sum the largest n values in a set of data, where n is a variable that can be easily changed. For convenience, the range B5:B16 is named data . At a high level, the solution breaks down into two steps: (1) extract the n largest values from the data set and (2) sum 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...
Data validation don't exceed total
Data validation rules are triggered when a user adds or changes a cell value. In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than...
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...
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...
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...
Sum range with INDEX
The INDEX function looks up values by position. For example, this formula retrieves the value for Acme sales in Jan: =INDEX(data,1,1) The INDEX function has a special and non-obvious behavior: when the row number argument is supplied as zero or null, INDEX retrieves all values in the column...
Sum across multiple worksheets
In this example, the goal is to sum total points for each student across five worksheets that all have the same structure. This can be accomplished with a 3D reference, as explained below. Standard reference Before we look at how 3D references work, let's look at the standard approach. To solve...
SUM function videos
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 to add a calculated field to a pivot table
Adding a calculated field to a pivot table is an alternative to adding a new column to the source data. It's especially useful when you don't have access to the source data, for example when you're connecting to a database. Let’s take a look. This pivot table shows sales data by Product. If we...
How to edit a formula
One thing you'll need to do in Excel is check and edit formulas to keep them in sync with other worksheet changes. Editing a formula in Excel is similar to editing other content. One difference is that you can update cell references using drag and drop. Let's take a look. Here we have a worksheet...
How to convert Booleans to numbers
In this video, we'll look at some ways you can convert TRUE and FALSE values in Excel to 1s and 0s. When working with more advanced formulas, especially array formulas, you need to know how to convert TRUE and FALSE values in Excel to their numeric equivalents, 1 and 0. This is best explained with...
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...
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 a mixed reference to create a running total
Another example of a situation where you may need a mixed reference is a running total. Let's take a look. With normal totals, you can just sum a range and be done. The SUM function adds together all the values in the range and reports the result. But what if you want to create a running total?...
How to use the COUNTIFS function
In this video, we'll look at how to use the COUNTIFS function to count cells that meet multiple criteria. Let's take a look. In this first set of tables, we have two named ranges , "number" and "color." In column G, I'll enter a formula that satisfies the conditions in column E. The COUNTIFS...
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...
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 "...
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.
