fbpx

Tech Tuesday: Summing Across Multiple Worksheets Is Different In Excel, Google Spreadsheets, And Mac Numbers

Share

I made a spreadsheet for my hours during this internship using Google Spreadsheets. I really liked using Google Spreadsheets because I could easily format the cell colors into my favorite neon pinks, yellows, and greens. But, I ran into a  big problem. I could not figure out how to sum up the totals for each week, which were on different worksheets, into one cells. I searched the Internet and found a helpful tutorial. Click here to watch it:  http://www.youtube.com/watch?v=3G86lgIy2_0. This procedure seemed kind of difficult, so I wanted to know if it would be easier to do it on Excel or Numbers on my Mac – it was. Here are the steps to sum across multiple worksheets in all three programs:

Google Spreadsheets:

1) Click in the cell you want your sum to go.

2) Click on the function button. Click Sum and put your curser between the two parentheses that show up in your function bar.

3) Click in the cell you want to add. If you are only adding one cell in that worksheet, you must select more than one cell and then go back to the actual cell you want to add. You can’t select just one cell from the outset.  I think this is a Google booboo, but it works if I select more than one cell and then go back and select the one cell I want.

4) Go into the formula bar and type an addition (+) sign.

5) Click on the next sheet and click on the information you want to add into the summation, add another plus sign. You will have to select more than one cell and then go back and select the cell you really want.

6) Repeat until you have added all of the information into the function bar. Your formula will look something like this:

GoogleScreenShot

7) Press enter.

Excel:

1) Click in the cell you want your sum to go.

2) Click the function button.

3) Click into the first cell you want to add into the summation.

4) Type an addition (+) sign.

5) Go to the next sheet and repeat until you have added all of the cells you want. Your formula will look something like this:

ExcelScreenShot

6) Press enter.

Numbers (Mac):

1) Click in the cell you want your sum to go.

2) Click the function button.

3) Click in the first cell of data you want to add into the summation.

4) Hold down the command button.

5) Go to each sheet and click on the cells of data you want to add, while still holding down the command button.

6) Do that until all of the information you want is added. Your formula will look something like this:

NumbersScreenShot

7) Press enter.

I hope that this has helped some of you, especially if this is something you have struggled with like I did.

10 responses to “Tech Tuesday: Summing Across Multiple Worksheets Is Different In Excel, Google Spreadsheets, And Mac Numbers”

  1. Your formulas are redundant. You don’t need both the sum function and the addition.

    You could instead simply do “=Week2!F30 + Week3!F30 + Week4!F30 + Week5!F30 + Week6!F30 + F30”

    Or instead “=SUM(Week2!F30, Week3!F30, Week4!F30, Week5!F30, Week6!F30, F30)”

    Doing it the way you wrote your formula, the spreadsheet app is adding all of the cells together then passing the result to the SUM function. The sum of a single number is that number so while your method worked, it introduced unnecessary overhead.

    • Yes.Thank you.Agreed but overall, thanks Josh so much as I was trying to do formulas in google sheets like I always did in Excel and it was driving me bonkers!!

  2. Is there any short cut like 3D formula in excel, in the case of spread sheets?
    It is a tedious job in case of monthly reports.

Leave a Reply

Your email address will not be published. Required fields are marked *