Add numbers

  The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences.

Suppose you want to calculate a price total for the inventory of a store or the total gross profit margins for all departments that are under budget for the year. There are several ways to add numbers.

What do you want to do?

Add numbers in a cell

Add all contiguous numbers in a row or column

Add noncontiguous numbers

Add numbers based on one condition

Add numbers based on multiple conditions

Add numbers based on criteria stored in a separate range

What happened to the Conditional Sum Wizard?

Add only unique values

Add numbers in a cell

Use the + (plus sign) arithmetic operator in a formula.

For example, if you type the following formula in a cell:

=5+10

The cell displays the following result:

15

Top of Page

Add all contiguous numbers in a row or column

If you have a range of contiguous numbers (that is, there are no blank cells), you can use the AutoSum Button image button.

  1. Click a cell below the column of numbers or to the right of the row of numbers.

  2. On the Home tab, in the Editing group, click AutoSum Button image, and then press ENTER.

Top of Page

Add noncontiguous numbers

If you have a range of numbers that might include blank cells or cells containing text instead of numbers, use the SUM function in a formula. Even though they might be included in the range that is used in the formula, any blank cells and cells that contain text are ignored.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.

  2. Select the example in the Help topic.

    Note    Do not select the row or column headers.

    Selecting an example from Help
    Selecting an example from Help
  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

A

B

Salesperson

Invoice

Buchanan

15,000

Buchanan

9,000

Suyama

8,000

Suyama

20,000

Buchanan

5,000

Dodsworth

22,500

Formula

Description (Result)

=SUM(B2:B3,B5)

Adds two invoices from Buchanan, and one from Suyama (44,000)

=SUM(B2,B5,B7)

Adds individual invoices from Buchanan, Suyama, and Dodsworth (57,500)

Note    The SUM function can include any combination of up to 30 cell or range references. For example, the formula =SUM(B2:B3,B5) contains one range reference (B2:B3) and one cell (B5).

Top of Page

Add numbers based on one condition

You can use the SUMIF function to create a total value for one range based on a value in another range. In the following example, you want to create a total only for the values in column B (Invoice) that correspond to values in column A (Salesperson) for the salesperson named Buchanan.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.

  2. Select the example in the Help topic.

    Note    Do not select the row or column headers.

    Selecting an example from Help
    Selecting an example from Help
  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

A

B

Salesperson

Invoice

Buchanan

15,000

Buchanan

9,000

Suyama

8,000

Suyama

20,000

Buchanan

5,000

Dodsworth

22,500

Formula

Description (Result)

=SUMIF(A2:A7,"Buchanan",B2:B7)

Sum of invoices for Buchanan (29000)

=SUMIF(B2:B7,">=9000",B2:B7)

Sum of large invoices greater than or equal to 9,000 (66500)

=SUMIF(B2:B7,"<9000",B2:B7)

Sum of small invoices less than 9,000 (13000)

The SUMIF function uses the following arguments

Formula with SUMIF function
Formula with SUMIF function

1. Range to evaluate: Check these cells to determine whether a row meets your criteria.

2. Criteria: The condition that the cells you evaluate must meet for the row to be included in the sum.

3. Range to sum: Add the numbers in these cells provided that the row satisfies the condition.

Top of Page

Add numbers based on multiple conditions

To do this task, use the SUMIFS function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.

  2. Select the example in the Help topic.

    Note    Do not select the row or column headers.

    Selecting an example from Help
    Selecting an example from Help
  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

A

B

C

D

Region

Salesperson

Type

Sales

South

Buchanan

Beverages

3571

West

Davolio

Dairy

3338

East

Suyama

Beverages

5122

North

Suyama

Dairy

6239

South

Dodsworth

Produce

8677

South

Davolio

Meat

450

South

Davolio

Meat

7673

East

Suyama

Produce

664

North

Davolio

Produce

1500

South

Dodsworth

Meat

6596

Formula

Description (Result)

=SUMIFS(D2:D11,A2:A11,"South",C2:C11,"Meat")

Sum of Meat sales in the South region (14719)

=SUM(IF((A2:A11="South")+(A2:A11="East"),D2:D11))

Sum of sales where the region is South or East (32753)

Note   The second formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

How the functions are used in the preceding example

=SUMIFS(D2:D11,A2:A11,"South",C2:C11,"Meat")

The SUMIFS function is used in the first formula to find rows in which "South" is in column A and "Meat" is in column C. There are three cases of this; in rows 7, 8, and 11. The function first looks at column A, which contains the regions, to find a match for “South.” It then looks at column C, which contains the food type, to find a match for “Meat.” Finally, the function looks in the range that contains the values to sum, D2:D11, and sums only the values in that column that meet those two conditions.

=SUM(IF((A2:A11="South")+(A2:A11="East"),D2:D11))

The second formula, which uses the SUM and the IF functions, is entered as an array formula (by pressing CTRL+SHIFT+ENTER) to find rows in which either one or both of "South" or "East" is in column A. There are seven cases of this; in rows 2, 4, 6, 7, 8, 9, and 11. Because this formula is an array formula, the + operator isn't used to add values; it is used to check for two or more conditions, at least one of which must be met. Then, the SUM function is used to add the values that meet these criteria.

Top of Page

Add numbers based on criteria stored in a separate range

To do this task, use the DSUM function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.

  2. Select the example in the Help topic.

    Note    Do not select the row or column headers.

    Selecting an example from Help
    Selecting an example from Help
  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

11

A

B

C

D

Region

Salesperson

Type

Sales

South

Buchanan

Beverages

3571

West

Davolio

Dairy

3338

East

Suyama

Beverages

5122

North

Suyama

Dairy

6239

South

Dodsworth

Produce

8677

South

Davolio

Meat

450

South

Davolio

Meat

7673

East

Suyama

Produce

664

North

Davolio

Produce

1500

South

Dodsworth

Meat

6596

Region

Salesperson

Type

Sales

South

Meat

Produce

Formula

Description (Result)

=DSUM(A1:D11, "Sales", A12:D13)

Sum of Meat sales in the South region (14719)

=DSUM(A1:D11, "Sales", A12:D14)

Sum of Meat and Produce sales in the South region (25560)

The DSUM function uses the following arguments.

Formula with DSUM function

1. Range to evaluate: The list from which you want to sum.

2. Field: The label of the column to sum.

3. Criteria: The range of cells that contains the conditions.

Top of Page

What happened to the Conditional Sum Wizard?

This add-in is no longer included with Microsoft Excel 2013. In earlier versions of Excel, you could use the Conditional Sum Wizard to help you write formulas that calculate the sums of values that met specified conditions. This functionality has been replaced by the Insert Function dialog box, located on Formulas tab in Function Library group, and other existing worksheet functions, such as SUMIFS and using a combination of SUM and IF together in a formula. For more information about using these functions to conditionally sum columns or rows of data, see the section Add numbers based on multiple conditions, earlier in this article.

Top of Page

Add only unique values

To do this task, use the SUM, IF, and FREQUENCY functions.

The following example uses the:

  • FREQUENCY function to identify the unique values in a range. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a 0 (zero).

  • IF function to assign a value of 1 to each true condition.

  • SUM function to add the unique values.

Tip    To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.

  2. Select the example in the Help topic.

    Note    Do not select the row or column headers.

    Selecting an example from Help
    Selecting an example from Help
  3. Press CTRL+C.

  4. In the worksheet, select cell A1, and press CTRL+V.

  5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

3

4

5

6

7

8

9

10

A

Data

986

456

67

1

34

689

456

56

67

Formula

Description (Result)

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,A2:A10))

Add the unique values in cells A2:A10 (2289)

Top of Page

Applies To: Excel 2013



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language