Home > Formulas > Math Trig > Sum Excel Sum Function Examples Examples show how to sum in Microsoft Excel, with a simple SUM function, or formulas that sum based on one or more criteria. First, for a quick overview, watch the video: 7 Ways to Sum in Excel. Next, scroll down for more Sum function videos, written steps, and free Excel workbooks. Author: Debra Dalgleish

Overview: 7 Ways to Sum in Excel

For a quick overview of 7 different ways to sum numbers with Excel functions, you can watch this 1-minute video. The blue links go to the written steps for each example.

--1) Grand Total a range of cells  

--2) Sum a simple range of cells

--3) Running Total

--4) Sum cells that match one criterion 

--5) Match with wildcard

--6) Sum cells that match multiple criteria

--7) Sum a Filtered List With SUBTOTAL

Sum a range of cells -- SUM Function

The quickest and easiest way to sum a range of cells is to use the Excel AutoSum button. It automatically enters an Excel SUM function in the selected cell. The SUM function totals one or more numbers in a range of cells.

The first example, below, shows how to use the AutoSum feature

1. Select the blank cell in the row below the cells that you want to sum, cell A5 in this example.

   

2. Click the AutoSum command on the Ribbon's Home tab,
   or use the keyboard shortcut: Alt + =

   

3. A SUM formula will appear in the active cell, with a reference to the cells above. In the screen shot below, there is a SUM formula in cell A5: =SUM(A1:A4)
   NOTE: If all cells are not automatically included, you can extend the frame, to select them.

   

4. Press the Enter key to complete the entry.

The SUM Function Setup

Instead of using the AutoSum command to insert the SUM function, you can type the function manually.

The SUM function setup (syntax) is: SUM(number1, [number2],...).

• It has one required argument: number1
• It also has optional arguments, enclosed in square brackets: [number2],..

In the example above (=SUM(A1:A4)), there is one argument -- a reference to cells A1:A4.

Function Notes

• The SUM function arguments can be cell references, or can be typed into the formula.
• The references can be a block of cells or a selection of non-contiguous cells.
• Cell references can be for the active worksheet, or cells on other sheets.
• SUM will ignore empty cells, and text values are treated as zero amounts
• The SUM function is in the Math and Trigonometry category (Microsoft site link)

SUM Function or Plus Sign ➕

You can also use the plus sign as an addition operator in Excel, to get a sum of numbers. Over the years, I've seen many people do addition with the plus sign in Excel, expecially if they frequently use a calculator.

For most Excel formulas, I recommend using the SUM function, instead of plus signs, because it is less likely to return an error. The examples below show how the plus sign formulas can cause problems.

✅ Small List of Numbers

The plus sign works well if there are only a few cells to include in the sum calculation.

For example, in the screen shot below,

• there is a SUM formula in cell A5: =SUM(A2:A4)
• and a plus sign formula in cell C5: =A2+A3+A4

Both formulas show the same result - a total of 10.

⛔ Text Values Cause Error

If there are text values in any of the cells that the formula refers to, a plus sign formula will return a #VALUE! error. If you use the SUM function, it ignores any text values, and returns the total for the numbers.

For example, in the screen shot below, someone type a space character in cells A3 and C3. Sometimes people do that when they want to clear a cell, instead of using the Delete key.

The SUM total in cell A5 is correct, but the plus sign formula returned an error, in cell C5.

⛔ Deleted Rows or Columns Cause Error

Another common activity in a spreadsheet is deleting a row or column that is no longer needed. That can cause #REF! errors in a plus sign formula.

For example, in the screen shot below, row 3 was deleted, to get rid of the "blank" cells from the previous example. .

The SUM total in cell A5 is still correct, and the cell references have changed:

• = SUM(As:A3)

However, the plus sign formula returned a #REF! error, because the deleted row's cell reference was replaced by #REF! in the formula.

• =A2+#REF!+A3

Fix numbers that don't add up

Some Excel values look like numbers, but don't add up, because Excel thinks they are text. Sometimes, you can fix the problem with Paste Special. Watch this short video tutorial, to see the steps

For the written steps, and other ways to fix the problem, go to the Numbers Don't Add Up page.

Grand Total a range of cells

In one quick step, you can calculate the row, column, and grand totals for a range of cells. Watch this short Excel Grand Totals video, to see how to do it. There are written instructions below the video.

Quick Grand Total for a range of cells

1. 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)



2. Click the AutoSum button on the Ribbon's Home tab. A SUM formula will be automatically entered for each Total.

Running Total

In this video, I show how to set up a running total formula, and lock the starting row. The written steps are on the Running Total and Rolling Total page.

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.

1. Select cell A5.
2. Enter the following formula:
      =SUM(A1:OFFSET(A5,-1,0))



3. Press the Enter key to complete the entry.
4. Insert a row above row 5
5. Type a number in cell A5, and it will be included in the total in cell A6

Sum cells that match criteria -- SUMIF

Here are 3 ways to sum cells that match criteria, using the Excel SUMIF function:

    - Match criterion exactly  

    - Match criterion in a string  

    - Match criterion using operator  

Tip: For examples on how to sum based on multiple criteria, go to the Sum cells with complex multiple criteria section, below.

SUMIF Function Setup

The SUMIF function setup (syntax) is: SUMIF(range, criteria, [sum_range])

• SUMIF has two required arguments:
  • range - cells to check for the criteria
  • criteria - value to use as criteria
• SUMIF also has one optional argument (in square brackets):
  • [sum_range] - cells to sum -- if omitted, values in range are summed

These arguments can be cell references, or can be typed into the formula.

Match criterion exactly

You 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.

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
5. Type a comma, to separate the arguments
6. 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
   
7. Type a comma, to separate the arguments
8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
9. The completed formula is:
     =SUMIF(A2:A10,"Pen",B2:B10)
   
10. 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 string

You 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".

1. Select the cell in which you want to see the total (cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Type:   SUMIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
5. Type a comma, to separate the arguments
6. 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
   
7. Type a comma, to separate the arguments
8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
9. Type a closing bracket. The completed formula is: =SUMIF(A2:A10,"*Pen*",B2:B10)
   
10. Press the Enter key to complete the entry
11. 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 the example below, see how to combine criteria in the SUMIF formula.

Example - Sum Rows Greater Than Set Amount

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.

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells B2:B10 will be checked
5. Type a comma, to separate the arguments
6. 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.
7. Type a comma, to separate the arguments
8. Select the cells that contain the values to sum
9. Type a closing bracket. The completed formula is:
     =SUMIF(B2:B10,">=10",C2:C10)
   
10. Press the Enter key to complete the entry
11. 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

In Excel 2007 and later versions, you can use the SUMIFS function to calculate a total for rows that meet two or more criteria. Watch this short video to see the steps. There are written instructions below the video.

For the full video transcript, go to the Sum Amounts With 2 Criteria Video page.

SUMIFS Function Setup

The SUMIFS function setup (syntax) is: SUMIFS(range, criteria, [sum_range])

• SUMIFS has three required arguments:
  • sum_range - cells to check for the criteria
  • criteria_range1 - cells to check for the first criterion
  • criteria1 - value to use as first criterion
• Additional pairs of criteria_range and criteria can be added, to sum based on multiple criteria

These arguments can be cell references, or can be typed into the formula.

Match multiple criteria

In this example, the SUMIFS function will sum the amounts in column D, based on 2 criteria:

• the status in column B is "Shipped"
• number of units in column C is greater than or equal to 10

Those criteria are entered in cells F6 (Shipped) and G6 (10)

Follow these steps to create the SUMIFS formula:

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMIFS(
4. Select the cells that contain the values to sum. In this example, cells D3:D10 will be summed
5. Type a comma, then select the cells that contain the values to check for the first criterion. In this example, cells B3:B10 will be checked
6. Type a comma, and then type the first criterion, "Shipped"
7. 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
8. Type a comma, and then type the second criterion:  " >=" & 10
9. Finish with a closing bracket: )
10. The completed formula is:
    =SUMIFS(D3:D10,B3:B10,"Shipped",C3:C10,">=" & 10)
11. Press the Enter key to complete the entry  

Use Cell References for Criteria

To make the formula more flexible, so you can easily change the criteria, refer to worksheet cells, instead of typing the criteria in the formula. For example:

• =SUMIFS(D3:D10,B3:B10, F6, C3:C10,">=" & G6)

Formulas With Table References

If you create formulas with table references, and then try to copy those formulas to adjacent columns, you might run into problems. This video shows the problem, and two ways to prevent it. There are written steps below the video.

Problems with Table References

In this sales summary, there is a SUMIFS formula in cell C5, which is showing the correct total for Bars sales in the East region.

=SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4)

However, if you point to the fill handle in cell C5, and drag to the right, the formula shows an incorrect total in cell D5.

If you check the formula in cell D5, all of the table references have shifted one column to the right, because the formula was dragged one column to the right.

=SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Product],D$4)

• Instead of Quantity, the formula is summing the Total Cost column
• Instead of looking for East in the Region column, it's looking in Category
• Instead of looking for Cookies in the Category column, it's looking in Product

None of those criteria are found, so the result is zero.

To prevent this problem of shifting table references, don't drag the fill handle to copy across.

Instead, use one of the following methods:

• Fill Right
• Copy and Paste

Fill Right

• Select the cell with the formula, and the cells to the right, where you want to copy the formula
• Press Ctrl+R to fill the formula to the right

Copy and Paste

• Select the cell with the formula, and press Ctrl+C to copy it
• Select all the cells where you want to copy the formula
• Press Ctrl+V to paste the formula

SUMIFS With Multiple AND/OR Criteria

In the list below, you could use a SUMIFS formula to total the rows where:

• City is New York

AND

• Category is Bars

The formula in cell G9 would be:

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

Formula with Multiple Criteria for AND / OR

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:

• city is New York AND Category is Cookies

OR

• city is New York AND Category is Bars

The 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.

1. Select the cell in which you want to see the total -- G9 in this example
2. To start the formula, type:   =SUM(SUMIFS(
3. Select the cells that contain the values to sum. In this example, cells D4:D15 will be summed
4. Type a comma, then select the cells that contain the values to check for the first criterion. In this example, cells B4:B15 will be checked -- they contain the city names
5. Type a comma, and then click on the cell with the first criterion -- G4
6. Type a comma, then select the cells that contain the values to check for the second criterion. In this example, cells C4:C15 will be checked -- they have the category
7. Type a comma, and then select the cells with the list for the second criterion -- G6:G7
8. Finish with 2 closing brackets: ))
9. The completed formula in cell G9 is:
   =SUM(SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6:G7))
   
10. Press the Ctrl + Shift + Enter keys to array-enter the formula -- the result will not be correct if you simply press the Enter key. 
11. 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. 

Sum Cells Match Multiple Criteria -- SUMPRODUCT

Match multiple criteria

Before SUMIFS was added to Excel, we could use the SUMPRODUCT function to calculate a total for rows that meet two or more criteria.

In the screen shot below, 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.

Here is the SUMPRODUCT formula that I entered in cell A9, to calculate the total amount for those records:

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

Sum Top 5 Numbers in 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.

1. Type the formula:
     =SUM(LARGE(A1:A50,ROW(INDIRECT("1:10"))))
2. 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..

1. In cell C1, type the number of top cells, e.g. 10
2. Type the formula:
     =SUM(LARGE(A1:A7,ROW(INDIRECT("1:"&C1))))
3. This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter

Sum Amounts in a Date Range

To sum amounts based on a date range, you can use the Excel SUMIFS function. Watch this video to see the steps, and the written steps 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. There are two examples below:

    - Total with SUMIFS

    - Total with SUMIF

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.

See more Date Range examples on the Sum or Count for a Date Range page.

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.

Sum 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 of filtered cells.

1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
3. Select the cell immediately below the column you want to sum.
4. 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.
5. 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.
  • You could also use function number 109 to SUM the numbers
• Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. The full list is shown in the section below.
6. 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.

Subtotal Function Numbers

The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. There are 11 functions that you can use as the first argument in the SUBTOTAL function. The list is alphabetical, so that might help you remember some of the numbers, without going to Excel's Help every time.

The functions are each listed twice.

The first group of functions is numbered 1-11. With these numbers, Excel will subtotal visible cells in a range where rows have been filtered

The second group of functions is numbered 101-111. With these numbers, Excel will subtotal visible cells in a range where rows have been manually hidden, or filtered

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:

1. Select the cell where you want the sum
2. Type =AGGREGATE(
3. In the list of functions, double-click on 9 - SUM, to add 9 as the first argument.
4. 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.

   

5. Type a comma, and select the range of cells that contain the data -- D2:D7 in this example.
6. 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 Filtered List

Example 1

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 (no longer available).

Incorporating 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)
• Press the Enter key to complete the formula entry.

Example 2

For another example of using SUMPRODUCT and SUBTOTAL together, see my blog post, Subtotal and Sumproduct with Filter. Sam shared his technique for doing additional sums or counts, based on the visible data in a filtered table.

Sam's workbook has a list with Product, Region and Amount fields. He created dynamic named ranges for the entries in each field, using INDEX and COUNTA.

You can get Sam's workbook in the Downloads section, below.

Video: 7 Ways to Sum in Excel

For a longer overview of 7 different ways to sum numbers with Excel functions, you can watch this 9-minute video.

Video Timeline (blue links go to written steps for each example

• 0:00 Introduction
• 0:09 1. Quick Grand Totals -- Grand Total a range of cells  
• 0:45 2. Sum Specific Cells -- Sum a simple range of cells
• 1:56 3. Running Total -- Running Total
• 3:28 4. Sum Specific Items -- Sum cells that match one criterion 
• 4:41 5. Sum Partial Match -- Match with wildcard
• 6:21 6. Sum With Multiple Criteria -- Sum cells that match multiple criteria
• 7:46 7. Sum in Filtered List -- Sum a Filtered List With SUBTOTAL 
• 09:27 Get the Sample File -- Download the 7 Ways to Sum sample file

Get the Excel Files ✅

• SUM Examples: Download the zipped Sum functions sample workbook. The workbook contains the examples for SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and AGGREGATE functions. The zipped file is in xlsx format, and does not contain any macros.
• SUMIFS Orders: Download the SUMIFS Orders sample workbook, to follow along with the Sum Amounts with 2 Criteria video. The zipped file is in xlsx format, and does not contain any macros.
• 7 Ways to Sum: Download the 7 Ways to Sum sample workbook, to follow along with the 7 Ways to Sum video. The zipped file is in xlsx format, and does not contain any macros.
• Sam's Workbook: To see Sam's workbook and formulas, for doing additional sums or counts, based on the visible data in a filtered table, download the SUMPRODUCT SUBTOTAL sample file.
• Table References: To see the problem with copying formulas with table references, download the Table Reference Problem workbook. The zipped file is in xlsx format, and does not contain any macros

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Function Tutorials

Functions List

Running Total

Filtered Rows, Sum

Calculation Options

Formulas, Getting Started

SUBTOTAL Function

AVERAGE

COUNT / COUNTIF