Excel -- Worksheet Functions -- Sum Cells

Sum a range of cells -- SUM   
Sum a range of cells -- OFFSET  
Grand Total a range of cells  
Sum cells that match criteria -- SUMIF  
    - Match criterion exactly  
    - Match criterion in a string  
    - Match criterion using operator  
Sum cells that match multiple criteria -- SUMPRODUCT  
Sum the Top 5 Numbers in a List 
Sum the Numbers in a Filtered List

 


AutoSum

Download zipped sample workbook


Sum a range of cells -- SUM

The SUM function will add the numbers in a range of cells. Its syntax is:
    =SUM(number1, number2,...number30).
The arguments (e.g. number1) can be cell references, or typed into the formula. The following example uses one argument -- a reference to cells A1:A4.

  1. Select the blank cell in the row below the cells that you want to sum, cell A5 in this example.
  2. Click the AutoSum button on the Standard toolbar
    • A SUM formula will appear in the active cell
  3. Press the Enter key to complete the entry.

    Note: Instead of using the AutoSum button, you could type the formula into the cell.


Sum a range of cells -- OFFSET

If you insert a row directly above the SUM formula in the previous example, the new row may not be included in the SUM. It may continue to sum cells A1:A4, and ignore A5. To ensure that new rows are included in the total, you can use the OFFSET function.

  1. Select cell A5.
  2. Enter the following formula:
       =SUM(A1:OFFSET(A5,-1,0))
  3. Press the Enter key to complete the entry.
  4. Insert a row above row 5
  5. Type a number in cell A5, and it will be included in the total in cell A6


Grand Total a range of cells

You can calculate row totals, column totals, and a grand total for a range of cell, all in one step.
  1. Select the range of cells, and the blank row below the range, and the blank cells in the column to the right (cells A1:D5 in the example at right)
  2. Click the AutoSum button on the Standard toolbar
    • A SUM formula will be entered for each Total


Sum cells that match criteria -- SUMIF

Match criterion exactly

You can calculate a total for rows that meet a specific criterion. In this example only the rows with Pen orders will be included in the total.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for text, so type the word in double quotes:   "Pen"
    Note: upper and lower case are treated equally
  7. Type a comma, to separate the arguments
  8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
  9. The completed formula is:
      =SUMIF(A2:A10,"Active",B2:B10)
  10. Press the Enter key to complete the entry

 

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:
    =SUMIF(A2:A10, B12, B2:B10)
if cell B12 contained the text — pen.

 

  

Match criterion in a string

You can add cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be summed, because they contain the string "pen".

  1. Select the cell in which you want to see the total (cell A12 in this example)
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters:   "*Pen*"
    Note: upper and lower case are treated equally
  7. Type a comma, to separate the arguments
  8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
  9. Type a closing bracket. The completed formula is: =SUMIF(A2:A10,"*Pen*",B2:B10)
  10. Press the Enter key to complete the entry
  11. The result will be 53, the total of rows that contain the string, "Pen"

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:
    =SUMIF(A2:A10,"*" & B12 & "*",B2:B10)
if cell B12 contained the text — pen. 

 

  

Criterion and operator

You can use an operator with a criterion. In this example only the rows where the number of sales reps is greater than or equal to ten will be included in the total.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells B2:B10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for rows where the number of visits is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes.
  7. Type a comma, to separate the arguments
  8. Select the cells that contain the values to sum
  9. Type a closing bracket. The completed formula is:
      =SUMIF(B2:B10,">=10",C2:C10)
  10. Press the Enter key to complete the entry
  11. The result will be 183, the total of rows with ten or more sales reps.

 

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to:
   =SUMIF(B2:B10,">=" & B12,C2:C10)
if cell B12 contained the number — 10. 

 

  

Sum cells that match multiple criteria -- SUMPRODUCT

Match multiple criteria

You can calculate a total for rows that meet two or more criteria. In this example only the rows where the status is "Active" and the number of visits is greater than or equal to ten will be included in the total.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMPRODUCT(--(
  4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A6 will be checked
  5. Type the first criterion:   ="Active"
  6. Type ),--(
  7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B6 will be checked
  8. Type the second criterion:   >=10
  9. Type ),--(
  10. Select the cells that contain the values to sum. In this example, cells C2:C6 will be summed
  11. Finish with closing brackets: ))
  12. The completed formula is:
    =SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=10),--(C2:C6))
  13. Press the Enter key to complete the entry

 


Sum the Top 5 Numbers in a List

Use the SUM and LARGE functions together, to add the largest numbers in the list.

Version 1 -- Few Top Numbers

If a few numbers are to be summed, e.g. top 3, you can type the numbers into the formula. For example:

=SUM(LARGE(A1:A7,{1,2,3}))  

The result is 70+60+50 = 180

Note: The second 50 is not included in the result, even though it is tied for 3rd place.

 

 

 

 

 

Version 2 -- Many Top Numbers

If many top numbers are to be summed you can include the INDIRECT function in the formula. In the INDIRECT function, use row numbers that represent the numbers you want to include. In this example, rows 1:10 are used, so the top 10 numbers in the referenced range will be summed.

  1. Type the formula:
      =SUM(LARGE(A1:A50,ROW(INDIRECT("1:10"))))
  2. This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter

Version 3 -- Variable Top Numbers

If a variable number of top numbers are to be summed you can include the INDIRECT function in the formula, as shown above, and refer to a cell that holds the variable..

  1. In cell C1, type the number of top cells, e.g. 10
  2. Type the formula:
      =SUM(LARGE(A1:A7,ROW(INDIRECT("1:"&C1))))
  3. This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter
 


Sum the Numbers in a Filtered List

After you filter the rows in a list, you can use the SUBTOTAL function to sum the numbers in the visible rows.

  1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
  2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
  3. Select the cell immediately below the column you want to sum.
  4. Click the AutoSum button on the Excel's Standard toolbar.
    • If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.
  5. A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column
    • The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. There is a 9 in this example, which tells Excel to SUM the numbers.
    • Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. Look in Excel's Help for a complete list.
  6. Press the Enter key to complete the formula entry.

Note: In Excel 2003, you can use the formula:
    =SUBTOTAL(109,B2:B9)
to subtotal visible cells in a range where rows have been manually hidden, or filtered.

Download zipped sample workbook
Worksheet Functions -- Sum Cells  
Worksheet Functions -- VLOOKUP  
Worksheet Functions -- INDEX / MATCH  
Worksheet Functions -- Count Cells  
Worksheet Functions -- INDIRECT  
 

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:40 PM