add rows with column greater than 3

Dear expert.

The number 1 below is in cell A1 and the number 10 is in cell B1.
I will enter in cell C1 a value from 1 to 6 and in cell D1 there should be a formula that adds rows 1 until the value inputed in C1. The formula sould look at column A and see if the value is less than or equal to value in C1, if so then it should add value in B1, this process should continue for range A1:B6.

Another way, is to have B1 as starting celll, and in C1 enter how many rows down we want sum. Example, if C1 reads 4 then it would sum B1:B4.

Br,
JP

1      10
2      22
3      33
4      44
5      55
6      66
7      77
easycapitalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
FlysterConnect With a Mentor Commented:
Yes, my sample above is one. This one is for numbers in column A that are less or equal to column C:

=SUMIF(A1:INDIRECT("A"&C1),"<="&C1,B1:INDIRECT("B"&C1))

In case you're wondering, the Indirect function allows you to use a cell value as a reference.
0
 
FlysterCommented:
Try this formula in D1:

=SUM(A1:INDIRECT("A"&B1))

Flyster
0
 
easycapitalAuthor Commented:
If C1 = 3 then D1 should = 10 + 22 + 33
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
easycapitalAuthor Commented:
If C1 = 5 then D1 should = 10 + 22 + 33 + 44 + 55.
0
 
FlysterCommented:
Here it is for column B:

=SUM(B1:INDIRECT("B"&C1))
0
 
FlysterCommented:
For the equal to or less that part, try this:

=SUMIF(A1:INDIRECT("A"&C1),"<="&C1,B1:INDIRECT("B"&C1))
0
 
Glenn RayExcel VBA DeveloperCommented:
To get the nth sum of values in column B add this formula to cell D1:
=SUM(INDIRECT("B1:B" & $C$1))

This is a variation of Flyster's post above in 40420705.

Regards,
-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
Or, if you wanted to avoid using an INDIRECT function, use this array formula:
=SUM(IF(ROW(B1:B7)<=$C$1,B1:B7,0))

This has to be entered with [Ctrl]+[Shift]+[Enter].
0
 
easycapitalAuthor Commented:
is there a possibility to the use the SUMIF function?
0
 
easycapitalAuthor Commented:
Will need to check again. I will revert.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.