You are here:

Office 2010/SUM Function Auto Update

Advertisement


Question
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!!

Answer
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  

Office 2010

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mary A. Lindsey

Expertise

New features of Office 2010

Experience

Microsoft Certified trainer with over 20 years of experience

Organizations
ASTD, ASTE

Education/Credentials
Microsoft Office 2010 Certified instructor

©2016 About.com. All rights reserved.