Cumulative SUM & restart count on condition

CASiddall

New Member
Joined
Nov 23, 2018
Messages
3
Hi

I have a row in which i input values on a daily basis, and i need these values to be added, until i insert a 'x' in a cell which will reset the sum total, thereafter the cumulative sum will only be after the last cell containing an 'x'.

Assuming i have 365 Columns representing each day in the year, what formula (or macro) can i use in cell 366 that can add values (including blank cells) in a row, but only sum the values after the last cell containing an "x"


Is this possible?
Thanks
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,340
Office Version
365
Platform
Windows
Hi, welcome to the forum!

Are you looking for something like this?

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">x</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">x</td><td style="text-align: center;;">3</td><td style="text-align: center;;">x</td><td style="text-align: center;background-color: #A9D08E;;">1</td><td style="text-align: center;background-color: #A9D08E;;">5</td><td style="text-align: center;background-color: #FFFF00;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;">x</td><td style="text-align: center;;">x</td><td style="text-align: center;;">x</td><td style="text-align: center;background-color: #A9D08E;;">3</td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;">5</td><td style="text-align: center;background-color: #A9D08E;;">4</td><td style="text-align: center;background-color: #A9D08E;;">5</td><td style="text-align: center;background-color: #FFFF00;;">17</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;background-color: #A9D08E;;">5</td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;">1</td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;">3</td><td style="text-align: center;background-color: #A9D08E;;">1</td><td style="text-align: center;background-color: #A9D08E;;"></td><td style="text-align: center;background-color: #A9D08E;;">4</td><td style="text-align: center;background-color: #A9D08E;;">5</td><td style="text-align: center;background-color: #FFFF00;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";">x</td><td style="text-align: center;background-color: #A9D08E;;">1</td><td style="text-align: center;background-color: #FFFF00;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">A2:K2,IFERROR(<font color="Green">LOOKUP(<font color="Purple">COLUMNS(<font color="Teal">A2:K2</font>),COLUMN(<font color="Teal">A2:K2</font>)-MIN(<font color="Teal">COLUMN(<font color="#FF00FF">A2:K2</font>)</font>)+1/(<font color="Teal">A2:K2="x"</font>)</font>),1</font>)</font>):K2</font>)</td></tr></tbody></table></td></tr></table><br />
 

CASiddall

New Member
Joined
Nov 23, 2018
Messages
3
Hi

Yes, this formula is exactly what I want, and it works 100% as intended, thank you very much for your assistance.

Best Regards
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
Slightly different computation, using the post #2 representation...

Control+shift+enter, not just enter, and copy down

=IFERROR(SUM(INDEX(A2:K2,MATCH(9.99999999999999E+307,1/(A2:K2="x"))):K2),0)
 

Watch MrExcel Video

Forum statistics

Threads
1,090,117
Messages
5,412,535
Members
403,432
Latest member
cr2141

This Week's Hot Topics

Top