Add numbers

Let's say you want to sum prices for all items not on sale in a store or sum gross profit margins for all departments under budget. 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

Add numbers based on multiple conditions with the Conditional Sum Wizard

Add unique values


Add numbers in a cell

To do this task, use the + (plus sign) arithmetic operator.

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

=5+10

The cell displays the following result:

15

Top of Page Top of Page


Add all contiguous numbers in a row or column

To do this task, use AutoSumButton image .

  1. Click a cell below the column of numbers or to the right of the row of numbers.
  2. Click AutoSum Button image on the Standard toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), and then press ENTER.

Top of Page Top of Page


Add noncontiguous numbers

To do this task, use the SUM function.

Example

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

ShowHow 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
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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 up to 30 cell or range references.

Function details

SUM

Top of Page 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, as in the following example.

Example

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

ShowHow 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
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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

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

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

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

Function details

SUMIF

Top of Page Top of Page


Add numbers based on multiple conditions

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

Example

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

ShowHow 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
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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)
=SUM(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11)) 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 formulas in the example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). 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.

Function details

SUM

IF

Top of Page 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.

ShowHow 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
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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

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

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

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

Function details

DSUM

Top of Page Top of Page


Add numbers based on multiple conditions with the Conditional Sum Wizard

If you want to summarize values in a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) based on specific conditions, you can use the Conditional Sum Wizard. For example, if your list contains sales amounts for different salespeople, the Conditional Sum Wizard add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program can help you create a formula that calculates the total sales amount for one salesperson.

  1. Click a cell in the list.
  2. On the Tools menu, point to Wizard, and then click Conditional Sum.

If the Conditional Sum command is not on the Wizard submenu on the Tools menu, load the Conditional Sum Wizard add-in program.

ShowHow?

  1. On the Tools menu, click Add-Ins.
  2. If the add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) that you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in.
  3. In the Add-Ins available box, select the check box next to the add-in that you want to load, and then click OK.
  4. If necessary, follow the instructions in the setup program.
  1. Follow the instructions in the wizard.

 Note    The formulas created by the Conditional Sum Wizard are array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After you edit these formulas, you must press CTRL+SHIFT+ENTER to lock in the formulas.

Top of Page Top of Page


Add 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. 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.
  • The SUM function to add the unique values.

Tip    To see a function evaluated step by step, select the cell containing the formula. Then, on the Tools menu, point to Formula Auditing and click Evaluate Formula.

Example

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

ShowHow 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
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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)

 Note   The formulas in this example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Function details

FREQUENCY

SUM

IF

Top of Page Top of Page

 
 
Applies to:
Excel 2003