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
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
Add all contiguous numbers in a row or column
To do this task, use AutoSum .
 Click a cell below the column of numbers or to the right of the row of numbers.
 On the Home tab, in the Editing group, click AutoSum , and then press ENTER.
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.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note 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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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
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.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note 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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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
Range to evaluate: Check these cells to determine whether a row meets your criteria.
Criteria: The condition that the cells you evaluate must meet for the row to be included in the sum.
Range to sum: Add the numbers in these cells provided that the row satisfies the condition.
Function details
SUMIF
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.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note 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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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
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
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note 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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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.
Range to evaluate: The list from which you want to sum.
Field: The label of the column to sum.
Criteria: The range of cells that contains the conditions.
Function details
DSUM
Top of Page
Add numbers based on multiple conditions with the Conditional Sum Wizard
If you want to summarize a range of cells based on specific conditions, you can use the Conditional Sum Wizard. For example, if your range of cells contain sales amounts for different salespeople, the Conditional Sum Wizard addin (addin: 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.
 Click a cell in the range of cells.
 On the Formulas tab, in the Solutions group, click Conditional Sum.
If the Conditional Sum command or Solutions tab are not available, then you need to load the Conditional Sum Wizard addin program.
Install and load the Conditional Sum Wizard addin program
 Click the Microsoft Office Button , click Excel Options, and then click the Addins category.
 Select Excel Addins in the Manage list box, and then click Go.
 In the AddIns available list, select the Conditional Sum Wizard check box, and then click OK.
 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
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, 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
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note 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 Formulas tab, in the Formula Auditing group, click the Show Formulas button.

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) 

Function details
FREQUENCY
SUM
IF
Top of Page