Add numbers

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. For a fast way to add numbers in cells, use the + (plus sign) in a formula.

For example, if you type =5+10, the cell shows 15 as the result.

Add all numbers in a row or column

If you have a range of adjacent numbers (that is, there are no blank cells), you can use AutoSum .

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

2. Click Home > AutoSum, and then press Enter.

Add non adjacent numbers

If you have a range of numbers that includes blank cells or cells that have 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 have text are ignored.

Example

This workbook shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

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

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

This workbook shows examples of the SUMIF function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

Add numbers based on multiple conditions

To do this task, use the SUMIFS function.

Example

This workbook shows examples of this function. Inspect them, change the existing formula, or enter your own formulas to learn how the function works.

How the functions are used in the SUMIFS 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 (to change arrays click Open in Excel) 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.

Add only unique values

To do this task, use a combination of 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.

Example

This workbook shows examples of these functions. Inspect them, change the existing formula, or enter your own formulas to learn how the function works.