Essentially, I would like to have a cell in A2 sum up the values in cells A1, B1, C1 where the number of cells it sums is a variable in another cell that may change. I would like the same to apply to cell B2, C2, D2, etc.
This may make more sense if you look at the attachment.
Thanks! This puzzle has been bugging me for a while.
it might be a better idea to show what is, not what is not! giving the total you expect
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Huh? Sorry, I don't understand your reply.
Do you want the variable to be truly Variable or will it be within a range? IE 1-5 or do you want it to be whatever you enter?
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the "Star" and adding to my reputation, Thanks!
Huh? Sorry, I don't understand your reply.well show one where it is then!and where 5 is a variable (in my example it is not)
if you post a couple of examples with 5 or whatever /wherever its supposed to be
then calculate the result manually an put it where you'd like the function to be, to give result.
it might be clear to you but i lost my crystal ball
Last edited by martindwilson; 05-01-2009 at 05:43 PM.
Ahh. I see your confusion Martin.
I would like to sum X number of cells, where X is the variable, and in my example it is set at 5. Each cell in the second line sums 5 cells. In the second example it is set at 3, where each cell in the second line sums 3 cells. X will need to range from 1-10.
Does this make it more clear?
Thanks for following up.
He is saying(I think)
If Cell L2 is 5 Then
Cell B2 would contain the following formula:
=Sum(B2:F2)
Cell C2 would contain the following formula:
=Sum(C2:G2)
etc etc
If Cell L2 is 3 Then
Cell B2 would contain the following formula:
=Sum(B2:D2)
Cell C2 would contain the following formula:
=Sum(C2:E2)
etc etc
Hope this helps you martindwilson,
Dan
split_atom18 is right in meaning - the actual formula needed can differ (i just realized a 'if' statement might work, but may get unwieldy)
For the example provided you could use the OFFSET function.
Assumes Column L is fixed.
=SUM(OFFSET(A1,0,0,1,$L2))
Awesome! Never realized offset had the width/height ability.
Thanks.
True but it's also Volatile so avoid it if you can, normally when using OFFSET an INDEX approach is equally viable and non-Volatile, using your example:Originally Posted by lhmome
A2: =SUM(A1:INDEX(A1:$IV1,$L2))
copy across ... apply to row 6 in the same manner
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
just worked that out myself as well, but make sure you have no cells containing numbers at least as far away from the last number in your summed range or it will be added on to the sum. so if L2=5 you are summing in range A1:K1, L1:O1 mustn't have numbers in.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks