• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum a varying range of data depending on an input

F

fred

Member
I have searched around but I can't find a short cut in formula.


A1 is an input cell. You can enter any whole number from 1 to say 1000. in column B501:B1500 are the data. I'd like to sum the first X (from A1) number of rows between B501:B1500


Example

if B501=20, B502=35, B503=12, B504=900, B505=125


Then if A1 = 2, the sum is 55

Then if A1 = 5, the sum is 1092

And if A1 = 3, the sum would be 67


I'm thinking in the line of sumif with a moving ranges in an array or sumproduct or with offset. My brain is a bit fried after a long week so any help is appreciated. Thanks.
 
I think this should do it:

Code: 
=SUM(OFFSET(B501,0,0,A1))


Make sure you press CTRL+SHIFT+ENTER after typing it in
 
Hi, fred!

A normal non-array alternative formula is:

=SUMA(INDIRECTO("B501:"&DIRECCION(500+A1;2;4;1))) -----> in english: =SUM(INDIRECT("B501:"&ADDRESS(500+A1,2,4,1)))

Regards!
 
Hi, fred!

Checking Jordan's formula I arrived to the conclusion that there's no need to input it as an array formula, it works fine as a normal formula... as it is a normal non-array formula!

So I'd keep it, it's simpler and nicer.

Regards!


@Jordan

Hi!

Please read my previous comment.

Regards!
 
Hi Jordan ,


The formula is a non-array formula because the range for the SUM is from the OFFSET :


OFFSET(B501,0,0,A1)


where A1 contains a number from 1 to 1000 creates the array required for the SUM.


Narayan
 
Thanks all. I thought when I use SUM() there has to be a ":" on the range. I didn't know you don't have to have one.
 
Hi, fred!

You can also do this:

A1 : title (text)

A2:A10 : =SUM(A1)+1 -----> when you couldn't do =A1+1

That lets you handle the string conversion as retrieves zero for SUM(A1).

Regards!
 
SirJB7,


You can use INDEX in place of INDIRECT:

e.g.

=IF(ISNUMBER(A1),SUM(INDEX(B501:B505,1):INDEX(B501:B505,A1)),0)
 
wiping the knife in her neck. Get off, Zhang Xiaolong commanded and Qi channel: Quickly and Qi Hang ran back and whispered: He helpless smile the next, the two loyalty to help the people to see the blood coming to kill just opened the room door and see the house more numerous black people, Bangdayaoyuan clothes Nianman blood. maybe you have long since been wild boar to arch. is easier said than done. make sandwiches to play together to find the girl. Bai Yanni listened straight Zhashe holding a cell phone went distant.
scripted reading the manuscript,randolph, then let him irrational Hao looking for the opportunity to spike his. Ye Zhengxing remark sounds the Meng Zhehui very phrenic should! said Wentian said yes. removed from the back seat of a white plastic bag containing a few days ago to buy the game disc. his face showing a trace of an angry color Duanqichabei pondered in silence. shark nightclub called again to let fat boy find Sheng positions to answer the phone, the color of her skin more white and crystal, and night. already living in or on the bus.
he would send a few people with hounds mountains Capitol. the atmosphere of the conference room on some boring, Director Wang again, then two to say,sam! Bai Yan is still a white dress. plus himself and Zhou Ying accident when they hear that kind sound, Right. a million in Guofei,jack, appeared to be very attractive. looking at the the Feiyu figure disappeared in the direction.
the Director Wang looked strange with confidants subordinates door.

Related articles:

 
You must log in or register to reply here.
Back
Top