Sum of cell range by manual option...

[Aaron DOJ]

Hello,
I want to be able to manually change the "Count" and depending on that number, a formula in "Total" will display the sum results from the top down within Column L. My range is L4;L18.

J K L
4 blah blah 175.00
5 blah blah 175.00
6 blah blah 50.00
Count: 2
Total: 400.00
In the case of the above is a perfect example of what I don't want to happen , I want the Total to display 350 because I chose Count 2, not Total of 400.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Just in case...the below is an example of what I do want to have happen :
J K L
4 blah blah 100.00
5 blah blah 125.00
6 blah blah 150.00
7 blah blah 50.00
8 blah blah 100.00
9 blah blah 50.00
Count: 4
Total: 425
I am not sure if I am just over thinking this or if its because Monday, but I can't seem to think of the solution.

Thank you for your time and thoughts!

 

[shg]

	K​	L​	M​	N​
4​	blah	blah	100​
5​	blah	blah	125​
6​	blah	blah	150​
7​	blah	blah	50​
8​	blah	blah	100​
9​	blah	blah	50​
10​		Count:	4​
11​		Total:	425​	M11: =SUM(M4:INDEX(M:M, ROW(M4) + M10 - 1))

That's the sum of the first four items.

 

[jtakw]

Hi,

This would also work:

Book1
	L	M	N
3		Count	Sum
4	100	4	425
5	125
6	150
7	50
8	100
9	50
Sheet237

Cell Formulas
Range	Formula
N4	=SUM(L4:INDEX(L4:L9,M4))

Change/adjust cell references/range to suit your data.

 

[Aaron DOJ]

Perfect SHG,

Thank you...I had to change it to =SUM(L4:INDEX(L:L, ROW(L4) + L20 - 1)) but works like a charm!!

 

[jtakw]

Did you try my simpler version formula in Post # 3?

Here, adjusted to your cell references:

Book1
	L	M
3		Sum
4	100	425
5	125
6	150
7	50
8	100
9	50
10
19
20	4
Sheet237

Cell Formulas
Range	Formula
M4	=SUM(L4:INDEX(L4:L18,L20))