Sum Rage. This should be easy

[white84]

I need to sum a range of cells to a max count of 25 cells, but don't start counting until cell has a value greater than $0. Values may start in any year/row

Ex

[TABLE="width: 120"]
<colgroup><col width="64" style="width: 48pt;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]2017
[/TD]
[TD="width: 96, bgcolor: transparent"] $ -
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2018[/TD]
[TD="bgcolor: transparent"] $ -
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2019[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2020[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2021[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2022[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2023[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2024[/TD]
[TD="bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2025[/TD]
[TD="bgcolor: transparent"] $ 10,535,745 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2026[/TD]
[TD="bgcolor: transparent"] $ 10,573,960 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2027[/TD]
[TD="bgcolor: transparent"] $ 15,382,862 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2028[/TD]
[TD="bgcolor: transparent"] $ 17,407,631 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2029[/TD]
[TD="bgcolor: transparent"] $ 21,970,667 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2030[/TD]
[TD="bgcolor: transparent"] $ 22,720,684 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2031[/TD]
[TD="bgcolor: transparent"] $ 41,887,887 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2032[/TD]
[TD="bgcolor: transparent"] $ 42,494,903 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2033[/TD]
[TD="bgcolor: transparent"] $ 55,223,078 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2034[/TD]
[TD="bgcolor: transparent"] $ 57,199,579 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2035[/TD]
[TD="bgcolor: transparent"] $ 67,473,618 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2036[/TD]
[TD="bgcolor: transparent"] $ 69,653,218 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2037[/TD]
[TD="bgcolor: transparent"] $ 82,316,126 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2038[/TD]
[TD="bgcolor: transparent"] $ 86,578,896 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2039[/TD]
[TD="bgcolor: transparent"] $ 100,128,464 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2040[/TD]
[TD="bgcolor: transparent"] $ 106,409,060 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2041[/TD]
[TD="bgcolor: transparent"] $ 101,581,370 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2042[/TD]
[TD="bgcolor: transparent"] $ 112,131,107 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2043[/TD]
[TD="bgcolor: transparent"] $ 117,364,532 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2044[/TD]
[TD="bgcolor: transparent"] $ 127,602,016 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2045[/TD]
[TD="bgcolor: transparent"] $ 116,521,822 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2046[/TD]
[TD="bgcolor: transparent"] $ 125,539,563 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2047[/TD]
[TD="bgcolor: transparent"] $ 149,051,540 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2048[/TD]
[TD="bgcolor: transparent"] $ 164,783,221 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2049[/TD]
[TD="bgcolor: transparent"] $ 154,489,529 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2050[/TD]
[TD="bgcolor: transparent"] $ 162,585,431 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2051[/TD]
[TD="bgcolor: transparent"] $ 151,844,177 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2052[/TD]
[TD="bgcolor: transparent"] $ 159,382,758 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2053[/TD]
[TD="bgcolor: transparent"] $ 146,588,341 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2054[/TD]
[TD="bgcolor: transparent"] $ 154,684,738 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2055[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2056[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2057[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2058[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2059[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(COUNTIFS($B$1:$B$38,">0"),SUM(OFFSET(INDEX($B$1:$B$38,MATCH(1,IF(ISNUMBER($B$1:$B$38),IF($B$1:$B$38>0,1)),0)),0,0,25)),0)

Is this what you have in mind?

 

[white84]

Yes Aladin! That's exactly what I was looking for! Thanks again! You seem to be the one who gets to my pleas for help first!

 

[Aladin Akyurek]

Yes Aladin! That's exactly what I was looking for! Thanks again! You seem to be the one who gets to my pleas for help first!

Great. You are very welcome.

 

[white84]

Similar issue, how can I apply a count or limit filter to any formula so when it is entered in the entire column it stops after the 25th occurrence? (After the first year of sales greater than $0).

 

[white84]

Re: Sum Range. This should be easy

Similar issue, how can I apply a count or limit filter to any formula so when it is entered in the entire column it stops after the 25th occurrence? (After the first year of sales greater than $0).

Bump to try this again.

Lets try a simple equation.

=if(A4>0,A4/$B$1,"")

Where B1 has the previous sum formula for 25 cells. I want this other formula to stop after the 25th occurrence.

Thanks in advance!

 

[Aladin Akyurek]

Re: Sum Range. This should be easy

Bump to try this again.

Lets try a simple equation.

=if(A4>0,A4/$B$1,"")

Where B1 has the previous sum formula for 25 cells. I want this other formula to stop after the 25th occurrence.

Thanks in advance!

Would you post 10 rows of (fake) data and use N = 5 for illustrative purposes instead of 25?