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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][td="bgcolor:#C0C0C0"]

M​

[/td][td="bgcolor:#C0C0C0"]

N​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]blah[/td][td]blah[/td][td]

100​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]blah[/td][td]blah[/td][td]

125​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]blah[/td][td]blah[/td][td]

150​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]blah[/td][td]blah[/td][td]

50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]blah[/td][td]blah[/td][td]

100​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]blah[/td][td]blah[/td][td]

50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td]Count:[/td][td]

4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td]Total:[/td][td="bgcolor:#CCFFCC"]

425​

[/td][td="bgcolor:#CCFFCC"]M11: =SUM(M4:INDEX(M:M, ROW(M4) + M10 - 1))[/td][/tr]
[/table]

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))