SUM since last zero

[Bas8178]

Hi everyone

Is it possible to SUM all cells after a particulat number (in this case zero)?

for instance

10
8
7
6
0
0
2
4
6

We would get 12.

Many thanks

Stu

 

[Aladin Akyurek]

Hi everyone

Is it possible to SUM all cells after a particulat number (in this case zero)?

for instance

10
8
7
6
0
0
2
4
6

We would get 12.

Many thanks

Stu

Define BigNum as referring to either 9.99999999999999E+307 or 9.99E+307.

Now invoke:

Control+shift+enter, not just enter...

C2:

=SUM(OFFSET(INDEX(A:A,MATCH(BigNum,A:A)),0,0,
     -(MATCH(BigNum,A:A)-MATCH(BigNum,1/(A2:INDEX(A:A,MATCH(BigNum,A:A))=0)))))

If you set B2 to:

=MATCH(BigNum,A:A)

=SUM(OFFSET(INDEX(A:A,B2),0,0,-(B2-MATCH(BigNum,1/(A2:INDEX(A:A,BigNum)=0)))))

 

[Bas8178]

Hi everyone

Many thanks, I went with Peters and it works a treat.

 

[MrVillareal]

Excel Workbook
	A	B	C
1	10	0	8
2	1
3	7
4	1
5	0
6	0
7	2
8
9	6
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Akinrotimi]

Hi,

The code below solves the problem:

Option Explicit
Sum since zero ()
' akinrotimi, 14/08/2011
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2735061
Application.ScreenUpdating = False
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-26]=0,"""",RC[-26])"
    Selection.AutoFill Destination:=Range("AA1:AA3000"), Type:=xlFillDefault
    Range("Ab1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IF(RC[-1]="""",""q"","""")=""q"",SUM(RC[-1]:R[10000]C[-1]),"""")"
    Selection.AutoFill Destination:=Range("AB1:AB10000")
    Range("AB1:AB10000").Select
    Columns("AB:AB").Select
     Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.TextToColumns Destination:=Range("AB1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    Range("ab1").End(xlDown).Offset(0, 0).Select
    Selection.Copy
    Range("a1000000").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Columns("AA:AC").Select
    Selection.ClearContents
    Range("a1000000").End(xlUp).Offset(0, 0).Select
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    Range("a1000000").End(xlUp).Offset(0, 0).Select
    Application.ScreenUpdating = True

Please confirm status after testing.

Regards

Rotimi

 

[Yahya]

Another way
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Value</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">A2:A10,MATCH(<font color="Green">0,A2:A10</font>)</font>):INDEX(<font color="Red">A2:A10,MATCH(<font color="Green">9.99999999999999E+307,A:A</font>)-1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

The code below solves the problem:

Rotimi

You did appear to ask for some feedback on your code, so ..

I don't see that it does solve the problem, for a number of reasons.

1. It isn't a valid piece of code. Did you mean it to start with something like this?
Sub Sum_since_zero()
.. rather than
Sum since zero ()

I presume you also accidentally left off the
End Sub

2. It may not be a problem for the OP but the code is limited to a few thousand rows. I'm not sure why you used 3000 in one section and 10000 in another or why you chose either of those two particular values.

3. The code will give incorrect results in a number of circumstances. For example, try the code with each of these sets of data starting in A1
0
2
2
2

The code gives a result of 0 but the sum since the last 0 is 6.



2
0
2
2
0
2

The code gives a result of 6 but the sum since the last 0 is 2.

Another note, not relating to the correctness of the code but a suggestion for the future. Selecting cells/ranges in code is rarely required and slows your code considerably.

If the problem was to be attacked by code then here is one way that would be more robust - and considerably faster.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Sum_Since_Last_Zero()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LastZeroRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> LastCell = Range("A" & Rows.Count).End(xlUp)<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", LastCell)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        LastZeroRow = .Find(What:="0", After:=.Cells(1, 1), _<br>            LookIn:=xlValues, LookAt:=xlWhole, _<br>            SearchDirection:=xlPrevious, SearchFormat:=False).Row<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> LastZeroRow > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> LastCell.Offset(1)<br>                .FormulaR1C1 = _<br>                    "=SUM(R[" & LastZeroRow - .Row & "]C:R[-1]C)"<br>                .Value = .Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[markmzz]

Another way (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;">Sum after last</td><td style="text-align: center;;">0</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--(<font color="Green">A1:A10&""=D1&""</font>)</font>)>0,IF(<font color="Red">A10&""<>D1&"",SUM(<font color="Green">A10:INDEX(<font color="Purple">A1:A10,1+MAX(<font color="Teal">(<font color="#FF00FF">A1:A10&""=D1&""</font>)*(<font color="#FF00FF">ROW(<font color="Navy">1:10</font>)-ROW(<font color="Navy">A1</font>)+1</font>)</font>)</font>)</font>),0</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Matty]

An old thread - resurrected for some reason - but I would approach this simply with:

=SUM(INDEX(A2:A10,MATCH(2,1/(A2:A10=0),1)+1):A10)

Array entered.

This solution assumes that there will always be at least one 0 somewhere in A2:A10.

Matty

 

[Akinrotimi]

Rotimi

You did appear to ask for some feedback on your code, so ..

Thanks for the valuable feedback Peter_SSs:

1.The header and end sub issues were my slip,sorry.

2.Yes I wanted to resolve the OPs issue.So I set the rows to 3,000 because the OPs did not say he has huge data.On the 10,000 rows,for optimality it can be set to 6,000 as that was my intention.This is because the first zero might actually occur on the 2,999th item in which case the code would calculate rows 2999 to 5999.

3.Row 1 is meant to be the header row without data,so we should not expect any data there.The code works perfectly under all conditions within the set range of 3,000 rows I used(whether there are empty cells or hundreds of zeros within the set range provided).

4.The run time of my code is 1.68 second so speed is not affected.

I also checked your solution and notice that it only sums up values below the last zero and this was not the OPs intention since the example provided even had multiple zeros.

My code solves the OP's issue perfectly.

Bas8178,

Please test the code.

Regards

Rotimi

 

[Peter_SSs]

3.Row 1 is meant to be the header row without data,so we should not expect any data there.

Fair enough. If that assumption is correct then that would certainly mean that your could would not return an incorrect result for the first example I suggested.

4.The run time of my code is 1.68 second so speed is not affected.

I'm not sure what you mean by speed is not affected.

On my machine your code ran faster than 1.68 seconds. Depending on how many rows of data I used (up to about 3,000) and where any zero(s) were located your code took from approximately 0.3 seconds to 0.8 seconds. On the same data samples and timing in seconds to 6 decimal places, my code was still registering 0.000000 seconds.

In that context I would contend that there is a significant speed difference.

The code works perfectly under all conditions within the set range of 3,000 rows I used(whether there are empty cells or hundreds of zeros within the set range provided).

I also checked your solution and notice that it only sums up values below the last zero and this was not the OPs intention since the example provided even had multiple zeros.

My code solves the OP's issue perfectly.

You point out that my code only sums the values below the last zero and suggest that this was not the OPs intention. I don't understand how you would conclude it was not the OP's intention when the thread title is "SUM since last zero", which you are confirming my code does, not "SUM since first zero", which your code does.

I'd also point out that the formula I suggested in post #3, which progressively sums from the previous 0, unlike any of the code or other formula suggestions, received the following response from the OP. So unless the OP returns and states otherwise I think it reasonable to conclude that was the intention.

Hi everyone

Many thanks, I went with Peters and it works a treat.