You are here:

- Home
- Computing/Technology
- Microsoft Software
- Office 2010
- SUM Function Auto Update

Advertisement

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.

B1=50

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

To get the value of “A50” I wrote the formula in cell B2

=CONCATENATE("A",(B1))

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

=CELL("contents",B2)

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

=SUM(A1:CELL("contents",B2))

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.

Mary

New features of Office 2010

Microsoft Certified trainer with over 20 years of experience**Organizations**

ASTD, ASTE**Education/Credentials**

Microsoft Office 2010 Certified instructor