You are here:

Office 2010/SUM Function Auto Update


I have a column of numbers, letís call it 1 to 300, which Iím looking to sum based on a constraint. Letís say that the first number is in column A1 making the last number is in A300. Now in another cell, letís call it B1, I have a number that Iím looking to determine how many of the cells to sum. Letís call that number in cell B1 ď50Ē. Now in the last cell, letís call it B3, I write my formula to sum the group of numbers in the ĎA columní based on the number in B1. So based on the number in B1, I would like the SUM function to sum cells A1 to A50. When I change the value in cell B1 to say ď60Ē I would like the function to sum cells A1 to A60. Etc.

Iím close on this but Iím having trouble with the way the SUM function written and I keep coming up with errors. Iím not sure how excel is understanding what cells to look at. Here is where I am, with reference to the numbers/cells I made up above.

A1=1, A2=2, A3=3Ö A300=300

To get the value of ďA50Ē I wrote the formula in cell B2
This now gives me ďA50Ē in cell B2 which will change when I change the value in B1.

Now to let excel see the contents of the cell Iím using the function

This is then embedded in my SUM function, but here is where Iím having the problem and getting the error.

What do I need to do to complete the function?
Or am I way off base here, and there is a better way of doing this?

Thank You!!

Hi Frank,

I am not sure if this is what you are searching for, but please see below

A        B          C
1   50   
2   =CONCATENATE("A",(B1))   =CELL("contents",B2)
3   =SUM(A1:C2)[This represents the formula you referred to in your initial email]

Let me know if this is what you are looking for.


Office 2010

All Answers

Answers by Expert:

Ask Experts


Mary A. Lindsey


New features of Office 2010


Microsoft Certified trainer with over 20 years of experience


Microsoft Office 2010 Certified instructor

©2017 All rights reserved.