Microsoft Excel - Formula to Calculate Sum of Cells in Separate Worksheets

This document explains how to calculate the sum or total when working with cell data located in multiple worksheets. In other words, how to take the value of a cell located in one worksheet and add it to the value of another cell located in another worksheet to come up with the total of the respective cells.

Example using Sheet1 and Sheet2

  1. Sheet1 has the following table setup.

  2. Sheet2 has the following table setup.

  3. To place the sum of the wins columns from Sheet1 and Sheet2  in cell B11 of Sheet2:
    • Click on B11 in Sheet2
    • Type in: =SUM(
    • Click on cell B3, then enter a +,  then click on B4 and enter a +. Follow this procedure until all the cells you want to add on the sheet are selected. NOTE: You must select each cell individually for the formula to work.

  4. Next click on the Sheet1 tab at the bottom and click on the cell you wish to add. In our case we clicked on B3. The cell selection gets added to the formula bar.

  5. Similarly, select all the cells you wish to add in Sheet1 making sure to add a + between each selection. The complete formula for wins will be: =SUM(B3+B4+B5+B6+B7+B8+Sheet1!B3+Sheet1!B4+Sheet1!B5+Sheet1!B6+Sheet1!B7+Sheet1!B8).

  6. Make sure to close the sum formula bracket with ). Next hit enter and the original cell will get filled with the sum. In our example, cell B11 in Sheet2 was filled with the sum of wins from Sheet1 and Sheet2.

  7. The same process can be replicated to fill cell C11 in Sheet2 for losses.

Keywords: excel formulas calculate values addition sum separate other different worksheets sheets   Doc ID: 2100
Owner: Jeff W.Group: DoIT Help Desk
Created: 2003-07-13 19:00 CDTUpdated: 2014-02-03 12:14 CDT
Sites: DoIT Help Desk, Southern Illinois University Edwardsville