0

I'm using Excel for Mac. I have this range of numbers in Sheet1 of a spreadsheet:

enter image description here

I want to sum A2:A11 in Sheet2, which works just fine:

enter image description here

However, if i add a new value into A12 of Sheet1, then sum in Sheet2 does not update. How can I get the sum in Sheet2 to update when new values are added at the bottom of column A in Sheet1?

3

There are several ways to approach this. Pick the one that works with how you plan to use column A of Sheet1.

=SUM(Sheet1!A:A)

This will add up all the numbers in column A. As long as the header in A1 is not a number, it will not affect the total. This is a good choice if you don't plan to put anything else in Column A

=SUM(Sheet1!A2:A100)

This will add up all the numbers from A2 to A100. empty cells will not affect the total. Change A100 to the address of the last cell that might be part of the data. It would be a good idea to use formatting on Sheet1 (borders or fill colours) to indicate the range that you have set aside for this use.

=SUM(Sheet1!A2:A11)

This is the formula you are already using. You can mark Sheet1 (using borders of fill colours) to remind you which cells can be used. When you want to insert another cell of data, do just that - use Insert to add another cell to the range A2:A11) The range will now be A2:A12, and the formula in Sheet2 will be automatically changed to SUM(Sheet1!A2:A12)

  • none of your suggestions work. Note that I need the formula to update when I insert a new row at the bottom of the range – luciano Nov 2 '17 at 18:52
  • @luciano It will work as long as the range used in the SUM function is larger than the number of rows that contain data, and you actually use Insert to insert a new row instead of just typing data in the next row. – Blackwood Nov 2 '17 at 19:02
  • apologies range wasn't longer than data, accepted as answer – luciano Nov 3 '17 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.