Search Contextures Sites

## Excel Sum Function Examples

Use SUM to calculate a simple total for a range of cells. Use SUMIF, SUMIFS, SUMPRODUCT for sums with criteria. Use SUBTOTAL and AGGREGATE to sum filtered lists.

- Sum a range of cells --
SUMFunctionGrand Totala range of cells

- VIDEO: Grand Total- Video: Running Total for a Range of Cells
- Running Total for a Range of Cells
- Sum a range of cells --
OFFSET- Sum cells that match criteria --
SUMIF

- Match criterion exactly

- Match criterion in a string

- Match criterion using operator- Sum cells that match multiple criteria --
SUMIFS

- VIDEO: SUMIFS Function- SUMIFS With Multiple AND/OR Criteria
- Sum cells that match multiple criteria --
SUMPRODUCT- Sum the
Top 5Numbers in a List- Video: Sum Amounts in a Date Range
- Sum Amounts in a
Date Range- Sum a Filtered List With
SUBTOTAL- Video: Sum a Filtered List With
AGGREGATE- Sum a Filtered List With
AGGREGATE- Sum Specific Items in a Filtered List
- Download the sample workbook for Sum Functions
- Excel Function Tutorials
## Sum a range of cells -- SUM Function

The SUM function will add the numbers in a range of cells. Its syntax is:

=SUM(number1, number2,...number30).The arguments (e.g. number1) for the SUM function can be cell references, or typed into the formula. The following example uses one argument -- a reference to cells A1:A4.

- Select the blank cell in the row below the cells that you want to sum, cell A5 in this example.
- Click the
AutoSumcommand on the Ribbon's Home tab

Or, use the keyboard shortcut:Alt + =A SUM formula will appear in the active cell, with a reference to the cells above. If the cells are not automatically included, you can drag over the cells, to select them.

Note: Instead of using the AutoSum button, you can type the SUM formula into the cell.## Grand Total a range of cells

You can calculate row totals, column totals, and a grand total for a range of cell, all in one step.

- Select the range of cells, and the blank row below the range, and the blank cells in the column to the right (cells A1:D5 in the example below)
- Click the AutoSum button on the Ribbon's Home tab. A SUM formula will be automatically entered for each Total.
See the simple steps in this short Excel Grand Totals video.

## Video: Running Total for a Range of Cells

To see a running total in each row of an Excel table, you can use the SUM function, with the starting row locked as an absolute reference. Watch this video to see how to set up the formulas, and lock the starting row. The written instructions are below the video.

## Running Total for a Range of Cells

Instead of calculating a total at the bottom of a range of cells, you can use a formula to show the running total in each row.

In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.

Enter this formula in cell D2, and copy down to cell D6:

=SUM(C$2:C2)

The formula uses an absolute reference to row 2 as the starting point -- C$2 -- and a relative reference to the ending point -- C2

This ensures that the starting point will not change when you copy the formula down to the rows below. Here is the formula in cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6

=SUM(C$2:C6)

## Sum a range of cells -- OFFSET

If you insert a row directly above the SUM function in the previous example, the new row may not be included in the SUM. It may continue to sum cells A1:A4, and ignore A5. To ensure that new rows are included in the total, you can use the OFFSET function with the SUM function.

- Select cell A5.
- Enter the following formula:

=SUM(A1:OFFSET(A5,-1,0))- Press the Enter key to complete the entry.
- Insert a row above row 5
- Type a number in cell A5, and it will be included in the total in cell A6
## Sum cells that match criteria -- SUMIF

Match criterion exactlyYou can calculate a total for rows that meet a specific criterion. In this example only the rows with Pen orders will be included in the total.

- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: SUMIF(
- Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for text, so type the word in double quotes: "Pen"

Note: upper and lower case are treated equally

- Type a comma, to separate the arguments
- Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
- The completed formula is:

=SUMIF(A2:A10,"Active",B2:B10)

- Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:

=SUMIF(A2:A10, B12, B2:B10)if cell B12 contained the text —

pen.

Match criterion in a stringYou can add cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be summed, because they contain the string "pen".

- Select the cell in which you want to see the total (cell A12 in this example)
- Type an equal sign (=) to start the formula
- Type: SUMIF(
- Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters: "*Pen*"

Note: upper and lower case are treated equally

- Type a comma, to separate the arguments
- Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
- Type a closing bracket. The completed formula is:
=SUMIF(A2:A10,"*Pen*",B2:B10)

- Press the Enter key to complete the entry
- The result will be 53, the total of rows that contain the string, "Pen"

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:

=SUMIF(A2:A10,"*" & B12 & "*",B2:B10)if cell B12 contained the text —

pen.## Criterion and operator

You can use an operator with a criterion. In this example only the rows where the number of sales reps is greater than or equal to ten will be included in the total.

- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: SUMIF(
- Select the cells that contain the values to check for the criterion. In this example, cells B2:B10 will be checked
- Type a comma, to separate the arguments
- Type the criterion. In this example, you're checking for rows where the number of visits is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes.
- Type a comma, to separate the arguments
- Select the cells that contain the values to sum
- Type a closing bracket. The completed formula is:

=SUMIF(B2:B10,">=10",C2:C10)

- Press the Enter key to complete the entry
- The result will be 183, the total of rows with ten or more sales reps.

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:

=SUMIF(B2:B10,">=" & B12,C2:C10)if cell B12 contained the number —

10.## Sum cells that match multiple criteria -- SUMIFS

Match multiple criteriaIn Excel 2007 and later versions, you can use the SUMIFS function to calculate a total for rows that meet two or more criteria. In this example only the rows where the status is "Active" and the number of visits is greater than or equal to ten will be included in the total.

A step by step video is below the written instructions.

- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: SUMIFS(
- Select the cells that contain the values to sum. In this example, cells C2:C6 will be summed
- Type a comma, then select the cells that contain the values to check for the first criterion. In this example, cells A2:A6 will be checked
- Type a comma, and then type the first criterion, "Active"
- Type a comma, then select the cells that contain the values to check for the second criterion. In this example, cells B2:B6 will be checked
- Type a comma, and then type the second criterion: " >=10"
- Finish with a closing bracket: )
- The completed formula is:

=SUMIFS(C2:C6,A2:A6,"Active",B2:B6,">=10")

- Press the Enter key to complete the entry
## Video: SUMIFS Function

## SUMIFS With Multiple AND/OR Criteria

In the list below, you could use a SUMIFS formula to total the rows where the city is New York AND the Category is Bars. The formula in cell G9 would be:

=SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6)Instead of just one category though, we would like to calculate the total for two or more categories. In this example, we'll calculate the total where:

- the city is New York
ANDthe Category is Cookies

OR- the city is New York
ANDthe Category is BarsThe two categories are entered in cells G6:G7 on the worksheet.

## Array-Entered Formula

To calculate the total, we'll wrap the SUMIFS formula with a SUM function, and enter the formula as an array.

- Select the cell in which you want to see the total -- G9 in this example
- To start the formula, type:
=SUM(SUMIFS(- Select the cells that contain the values to sum. In this example, cells
D4:D15will be summed- Type a
comma, then select the cells that contain the values to check for the first criterion. In this example, cellsB4:B15will be checked -- they contain the city names- Type a
comma, and then click on the cell with the first criterion --G4- Type a
comma, then select the cells that contain the values to check for the second criterion. In this example, cellsC4:C15will be checked -- they have the category- Type a
comma, and then select the cells with the list for the second criterion --G6:G7- Finish with 2 closing brackets:
))- The completed formula in cell G9 is:

=SUM(SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6:G7))

- Press the
Ctrl + Shift + Enterkeys toarray-enterthe formula -- the result will not be correct if you simply press the Enter key.- Curly brackets will be automatically added at the beginning and end of the formula, to show that it is array-entered. Do not type this brackets yourself.
## Video: SUMIFS AND / OR Formula

coming soon

Sum cells that match multiple criteria -- SUMPRODUCT

Match multiple criteriaIn Excel 2003 and earlier, you can use the SUMPRODUCT function to calculate a total for rows that meet two or more criteria. If you're using Excel 2007 or later, you should use the SUMIFS function, as described in the previous section.

In this example only the rows where the status is "Active" and the number of visits is greater than or equal to ten will be included in the total.

- Select the cell in which you want to see the total
- Type an equal sign (=) to start the formula
- Type: SUMPRODUCT(--(
- Select the cells that contain the values to check for the first criterion. In this example, cells A2:A6 will be checked
- Type the first criterion: ="Active"
- Type ),--(
- Select the cells that contain the values to check for the second criterion. In this example, cells B2:B6 will be checked
- Type the second criterion: >=10
- Type ),--(
- Select the cells that contain the values to sum. In this example, cells C2:C6 will be summed
- Finish with closing brackets: ))
- The completed formula is:

=SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=10),--(C2:C6))

- Press the Enter key to complete the entry
## Sum the Top 5 Numbers in a List

Use the SUM function and LARGE functions together, to add the largest numbers in the list.

## Version 1 -- Few Top Numbers

If a few numbers are to be summed, e.g. top 3, you can type the numbers into the formula. For example:

=SUM(LARGE(A1:A7,{1,2,3}))The result is 70+60+50 = 180

Note: The second 50 is not included in the result, even though it is tied for 3rd place.## Version 2 -- Many Top Numbers

If many top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function. In the INDIRECT function, use row numbers that represent the numbers you want to include. In this example, rows 1:10 are used, so the top 10 numbers in the referenced range will be summed.

- Type the formula:

=SUM(LARGE(A1:A50,ROW(INDIRECT("1:10"))))- This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter
## Version 3 -- Variable Top Numbers

If a variable number of top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function, as shown above, and refer to a cell that holds the variable..

- In cell C1, type the number of top cells, e.g. 10
- Type the formula:

=SUM(LARGE(A1:A7,ROW(INDIRECT("1:"&C1))))- This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter
## Video: Sum Amounts in a Date Range

To sum amounts based on a date range, you can use the SUMIFS function in Excel 2007 or later versions. Watch this video to see the steps, and the written instructions are below the video.

## Sum Amounts in a Date Range

To total the amounts in a specific date range, use the SUMIFS function (Excel 2007 and later) or the SUMIF function.

In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B.

## Use SUMIFS to Calculate Total for a Date Range

For Excel 2007, and later versions, you can use the SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:

- on or after the Start date

- AND
- on or before the End date.
Here is the formula that is entered in cell D5:

=SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$2, $A$2:$A$9,"<=" & $E$2)

- The first argument,
$B$2:$B$9, is the range with the numbers that we want to sum.- The 2nd argument,
$A$2:$A$9, is the range to check for criteria 1.- The 3rd argument,
">=" & $D$2, is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)- The 4th argument,
$A$2:$A$9, is the range to check for criteria 2.- The 5th argument,
"<=" & $E$2, is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to)In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.

To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

## Use SUMIF to Calculate Total for a Date Range

For Excel 2003, and earier versions, you can use the SUMIF function to calculate a total based on a single criterion. We'll use one SUMIF formula to total all the units where the sales date is:

- on or after the Start date
Then we'll use another SUMIF formula to subtract any values where there date is

- after the End date.
Here is the formula that is entered in cell D5:

=SUMIF($A$2:$A$9,">=" &$D$2,$B$2:$B$9)

- SUMIF($A$2:$A$9,">" &$E$2,$B$2:$B$9)

- The range,
$A$2:$A$9, contains the numbers that we want to sum.- The criteria,
">=" & $D$2, is the range with the Start date, and the operator to use with that value (greater than or equal to)- The range,
$B$2:$B$9, is the range to check for the date- The criteria,
">" & $E$2, is the range with the End date, and the operator to use with that value (greater than)In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.

To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

## Sum a Filtered List With SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function, instead of the SUM function, to sum the numbers in the visible rows.

- Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
- Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
- Select the cell immediately below the column you want to sum.
- Click the AutoSum button on the Ribbon's Home tab.

- If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.
- A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column

- The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. There is a 9 in this example, which tells Excel to SUM the numbers.
- Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. Look in Excel's Help for a complete list.
- Press the Enter key to complete the formula entry.

Note: In Excel 2003 and later versions, you can use the formula:

=SUBTOTAL(109,B2:B9)

to subtotal visible cells in a range where rows have been manually hidden, or filtered.## Video: Sum a Filtered List With AGGREGATE

The AGGREGATE function, introduced in Excel 2010, is similar to the SUBTOTAL function, but it has more functions, and can ignore error values, as well as hidden rows in the data.

Watch this video to see the steps for setting up an AGGREGATE formula, and the written instructions are below the video.

## Sum a Filtered List With AGGREGATE

After you filter the rows in a list, you can use the AGGREGATE function, instead of the SUM function, to sum the numbers in the visible rows. This function was introduced in Excel 2010.

Similar to the SUBTOTAL function, AGGREGATE ignores hidden rows, and offers several functions, like SUM or AVERAGE, for the selected data. However, it has 19 functions, compared to SUBTOTAL's 11 functions.

Unlike the SUBTOTAL function, AGGREGATE can be set to ignore errors, as well as hidden rows, and nested SUBTOTAL and AGGREGATE functions.

To sum the values in a filtered list, and ignore hidden rows and errors:

- Select the cell where you want the sum
- Type =AGGREGATE(
- In the list of functions, double-click on 9 - SUM, to add 9 as the first argument.
- Type a comma, and in the list of options, double-click on option 3 or option 7. In this example, 3 is the second argument, and the result will ignore hidden rows, errors, and nested AGGREGATE and SUBTOTAL functions.
- Type a comma, and select the range of cells that contain the data -- D2:D7 in this example.
- Type a bracket, to complete the formula, and press the Enter key.
The completed formula is:

=AGGREGATE(9,3,D2:D7)## Sum Specific Items in a Filtered List

Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site:

http://j-walk.com/ss/excel/eee/eee001.txtIncorporating that technique, SUMPRODUCT can be used to sum visible items in a filtered table. In the following example, column D has been filtered for amounts greater than 100. The following formula will sum the Total amounts, in rows that contain "Pen" in column A.

- Filter column D for amounts greater than 100.
- In cell A12, type: Pen
- In cell B12, enter the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)

-MIN(ROW(A1:A10)),,1)), --(A1:A10=A12),D1:D10)## Download the Sample File

Download the zipped Sum functions sample workbook. The workbook contains the examples for SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and AGGREGATE functions.

## Excel Function Tutorials

Sum Functions -- Sum Cells

VLOOKUP Function

INDEX / MATCH Functions

Count Functions -- Count Cells

INDIRECT Function## Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

## Search Contextures

Search Contextures Sites

More Function Tutorials

- Functions List
- 30 Functions in 30 Days
- AGGREGATE
- AVERAGE
- CHOOSE
- COUNT / COUNTIF
- HLOOKUP
- HYPERLINK
- IF
- IFERROR
- INDEX / MATCH
- INDIRECT
- ISFORMULA
- MIN / MAX
- PMT
- RANK
- Ratio
- Rounding
- SHEET and SHEETS
- SUBTOTAL
- SUM / SUMIF
- TEXT
- VLOOKUP

Last updated: January 4, 2015 8:42 PM