Excel sum numbers only

The SUMIF function returns the sum of cells in a range that meet a single condition. The first argument is the range to apply criteria to, the second argument is the criteria, and the last argument is the range containing values to sum. SUMIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.  Criteria can use a value in another cell, as explained below.

SUMIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and SUMIF requires a cell range for the range argument, you can't use an array.

SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate values that appear in the range argument (i.e. extract the year from dates to use in criteria) see the SUMPRODUCT and/or FILTER functions.

Basic Usage | Criteria in another cell | Not equal to | Blank cells | Dates | Wildcards |  Videos

Basic usage

The general pattern for SUMIF is:

The criteria is applied to cells in range. When cells in range meet criteria, corresponding cells in sum_range are summed. The sum_range argument is optional. If sum_range is omitted, the cells in range are summed instead.

Worksheet example

In the worksheet shown, there are three SUMIF formulas. In the first formula (G5), SUMIF returns total Sales where Name = "jim".  In the second formula (G6), SUMIF returns total Sales where State = "ca" (California).  In the third formula (G7), SUMIF returns the total of Sales > 100:

Notice the equals sign (=) is not required when constructing "is equal to" criteria. Also notice SUMIF is not case-sensitive; you can use "jim" or "Jim". Finally, notice that the last formula does not include sum_range, so range is summed instead.

Criteria in another cell

A value from another cell can be included in criteria using concatenation. In the example below, SUMIF will return the sum of all sales over the value in G4.  Notice the greater than operator (>), which is text, must be enclosed in quotes. The formula in G5 is:

Not equal to

To express "not equal to" criteria, use the "<>" operator surrounded by double quotes (""):

Again notice SUMIF is not case-sensitive.

Blank cells

SUMIF can calculate sums based on cells that are blank or not blank. In the example below,  SUMIF is used to sum the amounts in column C depending on whether column D contains "x" or is empty:

Dates

The best way to use SUMIF with dates is to refer to a valid date in another cell, or use the DATE function. The example below shows both methods:

Notice we must concatenate an operator to the date in E9. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the SUMIFS function, which can handle multiple criteria.

Wildcards

The SUMIF function supports wildcards, as seen in the example below:

See below for more SUMIF formula examples.

Notes

• SUMIF only supports one condition. Use the SUMIFS function for multiple criteria.
• When sum_range is omitted, the cells in range will be summed.
• Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"
• Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
• The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
• To find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk (i.e. ~?, ~*).
• SUMIFS requires a range, you can't substitute an array.

You use the SUMIF function to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5")

This video is part of a training course called Add numbers in Excel.

Syntax

SUMIF(range, criteria, [sum_range])

The SUMIF function syntax has the following arguments:

• range   Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).

• criteria   Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included - a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

  For example, criteria can be expressed as 32, ">32", B5, "3?", "apple*", "*~?", or TODAY().

  Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.

• sum_range   Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

  Sum_range should be the same size and shape as range. If it isn't, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:

  range	sum_range	Actual summed cells
  A1:A5	B1:B5	B1:B5
  A1:A5	B1:K5	B1:B5

Examples

Example 1

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Example 2

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See also

The SUMIFS function adds all arguments that meet multiple criteria

The SUMSQ function sums multiple values after it performs a mathematical square operation on each of them

The COUNTIF function counts only the values that meet a single criteria

The COUNTIFS function counts only the values that meet multiple criteria

IFS function (Office 365, Excel 2016 and later)

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Math & Trig functions

Excel functions (alphabetical)

Excel functions (by Category)

Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)

How to sum in Excel. 7 ways to sum in Excel. Watch short step-by-step videos, get written notes, download free Excel workbook with examples.

Overview: 7 Ways to Sum in Excel

For a quick overview of 7 ways to sum in Excel, you can see the steps in this video.

Get the 7 Ways to Sum sample workbook, so you can follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

Sum a range of cells -- SUM Function

The quickest way to sum a range of cells is to use the AutoSum button. It automatically enters a SUM function in the selected cell.

The SUM function totals one or more numbers in a range of cells.

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],..

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

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

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

To see a running total in each row of an Excel list, you can use the SUM function, with the starting row locked as an absolute reference. There are slightly different steps below,

Running Total in Worksheet List

For a worksheet list (not a named Excel table), this video shows how to set up the running total formula, and lock the starting row. The written steps are below. For the video transcript, go to the Running Total Video page.

Running Total - Worksheet List

For a worksheet list (not a named Excel table), follow these steps to create a running total. 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
• Copy the formula down to cell D6

How the Formula Works

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

Running Total in Excel Table

For a named Excel table, we can't use the worksheet list formula in the previous section. First, I'll show you the problem with that formula, and then you'll see the formula that works in a named table.

Problem with Running Total

After you enter the formula in cell D2 of the named Excel table, it automatically fills down, and the running total looks correct.

But, as soon as you start a new row at the bottom of the table, the formula in the last row changes.

Before the row was added, cell D6 had this formula:

As soon as the next entry was started in row 7, the formula in D6 automatically changed. Now it has an incorrect ending reference to C7, instead of C6:

As each new row is added, the formulas in the bottom rows keep changing, to show the latest row number.

Running Total Formula for Named Table

To avoid that problem, we'll use a slightly different formula for a running total in a named Excel table.

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
• The formula automatically fills down to cell D6
• All cells in column D show the same formula

How the Formula Works

The formula uses an absolute reference to a heading cell as the starting point -- C$1

• To prevent problems if a new data row is added at the top, the starting cell is in the table heading row
• Because the heading cell contains text, its value is treated as zero, and won't affect the running total

For the ending point, there is a structured table reference -- [@Amt]

• This is a reference to the Amt cell in the current row
• With that table cell reference, there's no problem when new rows are added to the Excel table

This screen shot shows that when a new row is started, cell C6 continues to show its original formula, and the running total amounts are correct in each row

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 criteris:

    - Match criterion exactly  

    - Match criterion in a string  

    - Match criterion using operator  

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 examples below, see how to combine them in the SUMIF formula.

Example 1

Example 2

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

Example 2 - Rolling 12 Month Total

In this example only the rows for the previous 11 months, and the current month, will be included in the total. This creates a Rolling Total.

The dates are in column A, and the list must be sorted by date. The monthly amounts are in column B.

1. Select the first cell in which you want to see the rolling total -- cell C2 in this example
2. Enter the following formula, and press Enter:
   =SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2).
3. Copy the formula down to the last row with data.
4. Each row shows the Rolling Total for the latest 12 months (if available)

How It Works

The formula checks the dates in column A, starting in row 2 (A$2), and down to the current row (A2)

=SUMIF(A$2:A2

The DATE function calculates the date that is 11 months prior to date in current row

DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

The >= operator checks for dates that are greater than or equal to that date,

">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))

For rows that meet the criterion, the formula sums the amounts in column B, starting in row 2 (B$2) down to the current row (B2)

B$2:B2

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.

Match multiple criteria

In this example only the rows where the status is "Shipped" and the number of units 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:   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  

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

• 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 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 AND the Category is Cookies
• the city is New York AND the 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 that match multiple criteria -- SUMPRODUCT

Match multiple criteria

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

1. Select the cell in which you want to see the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMPRODUCT(--(
4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A6 will be checked
5. Type the first criterion:   ="Active"
6. Type ),--(
7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B6 will be checked
8. Type the second criterion:   >=10
9. Type ),--(
10. Select the cells that contain the values to sum. In this example, cells C2:C6 will be summed
11. Finish with closing brackets: ))
12. The completed formula is:
    =SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=10),--(C2:C6))
    
13. 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.

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

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.

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.
• Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. The full list is shown in my blog post Total a Filtered List.
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.

The functions are each listed twice. The second group of functions is numbered 101-111.

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

Get the Sample 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

More Function Tutorials

Functions List

Calculation Options

Formulas, Getting Started

SUBTOTAL Function

AVERAGE

COUNT / COUNTIF

• You can sum up entire columns or rows in Microsoft Excel using the AutoSum feature. 
• The AutoSum tool automatically selects a column or row of numbers, but you can select any set of numbers by clicking and dragging with the mouse.
• You can also manually sum a series of numbers in Excel by typing in a simple SUM formula.
• You can view the details of your calculation by clicking the sum cell and looking at the formula bar at the top of the screen. 
• Visit Business Insider's Tech Reference library for more stories.

You can sum a series of numbers in Microsoft Excel manually by typing in a simple formula, or automatically by using the AutoSum tool in the toolbar on your screen. 

Both methods will always give you the same result, so use whichever one is more convenient for you.

Here's how to do it on a Mac or PC computer. 

Check out the products mentioned in this article:

Apple Macbook Pro (From $1,299.00 at Apple)

Acer Chromebook 15 (From $179.99 at Walmart)

iPhone 11 (From $699.99 at Apple)

Samsung Galaxy S10 (From $699.99 at Walmart)

How to use AutoSum in Excel

AutoSum works best when you have a column or row of numbers you want to add up.

1. Click the empty cell underneath the column of numbers that you want to add up. Or, if you want to sum a row of numbers, click the empty cell to the right of the series.

2. On the "Home" tab, click the AutoSum button (which looks like a sigma sign) in the toolbar at the top of your screen.

Steven John/Business Insider

3. You should see Excel draw a selection box around the numbers to be added. If the wrong numbers are selected, you can click and drag the mouse to choose the correct cells.

Dave Johnson/Business Insider

3. Press "Enter" on your PC keyboard, or "Return" if you're using a Mac.

Your cells don't have to be laid out consecutively in a row or column to be added up — instead of dragging down a columb or across a row, you can also click on non-consecutive cells throughout your spreadsheet before hitting "Return" or "Enter" on your keyboard. 

How to manually sum in Excel

1. Click the cell you want the sum to appear in and click "=."

2. Type a number or click a cell that has a value.

3. Type "+."

4. Type another number or click the next cell that has a value.

5. Repeat until you have entered all the values you want to sum, and then press "Enter" on a PC keyboard or "Return" on a Mac keyboard. 

Dave Johnson/Business Insider

To check your work, click the cell with the sum and look at the formula bar atop the screen. It will show the details of the calculation.

Related coverage from Tech Reference:

For someone fairly comfortable with Excel, summing up a list of cells is as easy as pie.

But when it comes to summing up cells based on a particular condition, you might need to add a little more knowledge of functions to your stack.

As hard as it may seem, filtering out and summing only positive numbers from a list of cells is actually quite easy.

No, you don’t need filters or complicated nested IFs for this. Excel is well equipped with formulas to let you quickly sum positive numbers in a range while filtering out all the negatives.

In this tutorial we will show you two ways in which you can sum only positive numbers in Excel:

It’s time to let negativity go!

Method 1: Calculate the Sum of Positive Numbers in Excel – Using SUMIF

Suppose you have a dataset as shown below and you want to sum all the positive numbers in column B.

Excel’s SUMIF function lets you add up numbers that match specified criteria in a range of cells. As such, this is the best way to selectively add up positive numbers.

Here’s the syntax for the SUMIF function:

In this function,

• range is the range of cells containing the data you want the function to work on.
• condition is the condition that you want to be satisfied in order to include a cell in the sum.
• sum_range is an optional parameter. To avoid confusion, it is enough to know that you don’t really need it when finding the sum of positive numbers.

If all three parameters are given, then the SUMIF function checks each cell in the range to see if it matches the condition. If it does, then the function takes the corresponding cell value in sum_range and includes it in the sum.

If just the first two parameters are given, then the SUMIF function goes through each cell in the range and sums up only those cells that match the condition. It finally returns the sum of all cells in the given range that match the criteria.

If you want to find the sum of positive numbers, the condition should be “>0”, because, in order for a number to be considered as positive, it has to be more than 0.

So to sum up only positive values from the range B2:B10, the SUMIF function will be:

Notice that we did not include the third parameter here.

Let us see now how we can apply the SUMIF function, to sum up the positive numbers in our sample dataset:

1. Click on the cell where you want the result to be displayed. In our example, it will be cell B11.
2. Type the ‘equal to’ sign (=), followed by SUMIF and an opening bracket: =SUMIF(
3. Next, select the range of values that you want to find the sum from. In our example, you have to select cells B2 to B10.
4. A reference to cells B2: B10 should appear after the opening bracket in cell B11.
5. Insert a comma (,) followed by the condition “>0”
6. Insert a closing bracket. Your complete formula in cell B11 for our example should be =SUMIF(B2:B10, “>0”).
7. Press the Return key.

You should now see the resulting sum of positive numbers in cell B11.

In case you want to sum all the negative values, you can use the same formula with a minor change:

Method 2: Adding only the Positive Numbers using VBA

If you are more of a coder at heart, then this method is for you.

Using VBA, you can create a macro to quickly sum up positive numbers in a range.

Here’s the VBA code that you can use:

This code takes a selection of cells and sums up the values of cells containing only positive numbers in that selection.

It then asks the user to select the cell where they want the result to appear. Once the user selects the cell, the code puts the result in the selected cell.

In case you need to sum all the negative numbers in a range in Excel, change the SumIf(rng, “>0”) part to SumIf(rng, “<0”) in the second-last line in the VBA macro code

To apply the above script, to your dataset, follow these steps:

1. From the Developer Menu Ribbon, select Visual Basic.
2. Once your VBA window opens, Click Insert->Module. Copy the code given above and paste it into the module window.
3. Your code is now ready to run and use whenever needed. Close the VBA window.
4. Now go to your worksheet and select the range of cells containing the numeric values you want to work on (cells B2:B10 in our example).
5. Click on Macros, under the Developer tab.
6. This will open the Macros dialog box. Select the name Sum_only_positive_numbers from the list of macros displayed.
7. Click OK.
8. Your code should now run. You will see a prompt asking you to select the cell where you want the result displayed. Select cell B11 in our example.
9. You should see the result in your selected cell.

Note that this method permanently changes the values in your worksheet. So there’s no way that you can undo it once the change is made.

Once you’re done writing the script you can re-use it as many times as you need to. For this, you can add the macro to your Personal Macro Workbook, attach it to a macro button, or add it to your Quick Access Toolbar (QAT).

If you need to use this macro multiple times, you can create a quick access button. Using this button, you can quickly find the sum of positive numbers in a range of cells whenever you need to.

Adding a Quick Access Button for the Macro

To add a button to quickly access the above macro, follow these steps:

1. Navigate to File->Options.
2. This will open the Excel Options dialog box. Select the Quick Access Toolbar option from the list on the left side of the dialog box.
3. Select Macros from the ‘Choose Commands From’ field.
4. Find the macro named Sum_only_positive_numbers from the list of macros on the left.
5. Click the Add button. This will add the macro Sum_only_positive_numbers to the Quick Access Toolbar. You should now see the macro’s name in the list on the right side of the dialog box.
6. Click OK to close the File Options dialog box.
7. You will now see that the macro icon has been added to the Quick Access Toolbar.

Whenever you select a range of number cells and click on this quick access button, your code will run and you can use it to find the sum of positive numbers in your selected range.

In this tutorial, we saw two easy ways to selectively sum up the positive numbers in a range of cells. The first method involves the use of the SUMIF function with the condition “>0”. The second method involves the use of VBA code.

Note that you can select any range of numbers in any number of columns. The above two methods will help add up all the positive numbers in the entire selection.

Selectively summing up numbers need not always be complex. We hope this tutorial helped clear that out and gave you the confidence to try out more fun tricks with Excel.

Other Excel tutorials you may like: