How do I add up different cells from different sheets within one workbook?

Britney Starr 5 Reputation points


 I need to add multiple cells (not all in the same location) on multiple sheets for a grand total on a separate sheet all within the same workbook. I have tried =SUM( and then clicking on each sheet and then each cell and finishing by pressing enter and I get an error message. I tried =SUM( and clicking on each cell in each sheet and typing a + sign after each cell. I still get an error message. Excel is automatically putting apostrophes around my sheet names. Is this messing me up? This seems like such an easy task, but I am getting very frustrated! 

      Thank you in advance for any help you can provide.



A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,601 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. AllenXu-MSFT 17,421 Reputation points Microsoft Vendor

    Hi @Britney Starr,

    What error message you got? Kindly share here and it is important for us to help you troubleshoot.

    Generally, we can get this like the following screenshot. I got the sum of A1, B1 and C1 of Sheet1 in the A1 of Sheet2. Is there any difference between your action and mine?


    Also, have a try to directly apply the formula to the cell instead of mouse-clicking and enter-pressing. In my example, if I copy the formula =SUM(Sheet1!A1+Sheet1!B1+Sheet1!C1) and apply it to the Sheet2 A1 cell, it also works.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Tanay Prasad 2,110 Reputation points


    Here's how you can do it step by step:

    Open the sheet where you want to display the grand total.

    Select the cell where you want the grand total to appear.

    Start typing the formula "=SUM(".

    Now, switch to the first sheet you want to include in the calculation by clicking on its tab.

    Select the cell you want to add to the grand total.

    Press the "+" key.

    Switch to the next sheet and select the next cell to be added.

    Repeat steps 6 and 7 for each additional sheet and cell you want to include in the grand total.

    Once you have selected all the cells from different sheets, close the formula with a closing parenthesis ")".

    Press Enter to calculate the grand total.

    It's exactly how AllenXu has shown in the video (in this thread)

    Best Regards.

    0 comments No comments