Add numbers

Microsoft Office Excel 2003

previous page next page

You can add numbers as you type them into a cell. For example, type =5+10 in a cell to display the result 15.

Show Add all numbers in a contiguous row or column

Use AutoSum Button image to do this task.

Click a cell below the column of numbers or to the right of the row of numbers.
Click AutoSum on the Standard toolbar, and then press ENTER.

Show Add numbers that are not in a contiguous row or column

You can use the SUM function to do this task as in the following example.

Example

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

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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 2 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.

For more information, see the SUM function.

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

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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

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 the row satisfies the condition.

For more information, see the SUMIF function.

Show Add numbers based on multiple conditions

You can use the IF and SUM functions to do this task, as in the following example.

Example

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

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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. 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.

For more information, see the functions SUM and IF functions.

Show Add numbers based on criteria stored in a separate range

You can use the DSUM function to do this task, as in the following example.

Example

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

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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: is the range of cells that contains the conditions.

For more information. see the DSUM function.

Show Add numbers based on multiple conditions with the Conditional Sum Wizard

If you want to summarize values in a list 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 program can help you create a formula that calculates the total sales amount for one salesperson.

Click a cell in the list.
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, you need to load the Conditional Sum Wizard add-in program.

How?
1. On the Tools menu, click Add-Ins.
2. If the add-in 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 you want to load, and then click OK.
4. If necessary, follow the instructions in the setup program.
Follow the instructions in the wizard.

Note The formulas created by the Conditional Sum Wizard are array formulas. After you edit these formulas, you must press CTRL+SHIFT+ENTER to lock in the formulas.

previous page start next page