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 matching columns
At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range data C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range: =SUMPRODUCT(data) // all data, returns 387 To apply a...
SUMIFS with multiple criteria and OR logic
In this example, the goal is to sum the value of orders that have a status of "Complete" or "Pending". This is a slightly tricky problem in Excel because the SUMIFS function is designed for conditional sums based on multiple criteria, but all criteria must be TRUE in order for a value to be...
Score quiz answers with key
This formula uses the named range key (C4:G4) for convenience only. Without the named range, you'll want to use an absolute reference so the formula can be copied. In cell I7, we have this formula: =SUM(--(C7:G7=key)) working from the inside-out, this expression is evaluated first: C7:G7=key //...
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...
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 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...
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...
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 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...
Sum if not blank
In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e. not empty). A good way to solve this problem is to use the SUMIFS function . However, you can also use the SUMPRODUCT function or the FILTER function , as explained below. Because SUMPRODUCT and...
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...
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...
Sum entire column
In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference. Full column references Excel supports " full column " like this: =SUM(A:A) // sum all of column A =SUM(C:C) // sum all of column C =SUM(A:C) // sum all...
Count missing values
In this example, the goal is to count the number of names in the range B5:B16 (Invited) that are missing from the range D5:D12 (Attended). This problem can be solved with the COUNTIF function or the MATCH function, as explained below. Both approaches work well. The advantage of the MATCH approach...
Sum every n rows
In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET function. To sum...
SUM function videos
How to calculate an average value
In this video we'll look at how to calculate an average value. Let's take a look. In this worksheet we have a list of 16 properties, each with a price and other information. Let's calculate an average price. First, I'll create a named range for the prices. This makes the formulas easier to read and...
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...
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 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 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...
How to trace a formula error
In this video we'll look at how to trace a formula error. Here we have a simple sales summary for a team of salespeople over a period of 4 months. You can see that we have monthly totals in the bottom row and totals for each salesperson in the last column. Below the table, we have a sales target...
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...
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 "...
How to use text formatting in Excel
In this lesson we'll look at the Text format. It may seem strange to format numbers as text, but there are times when it makes sense. The Text format displays numbers exactly as they were entered. Let's take a look. In column B of our table we have a set of numbers that are displayed as we like,...
How to make an exact copy of a formula
When you copy and paste formulas, Excel will change all relative references to reflect the new location of the formula. Usually, this is exactly what you want. However, there are times when you'll want to copy a formula exactly so that cell references don't change. Let's take a look at a few ways...
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.
