Progressive summing basing on cell value of another column

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
155
Office Version
2016, 2011, 2007
Platform
Windows
Hi,

I have a range of numbers at A2:A10. Column B shows the progressive sum (e.g. B2=A2, B3=B2+A3, B4=B3+A4 etc). Column C will either be blank or have a certain text "ABC".

I would like the progressive sum result at column D instead of B with some criteria. I need a formula at D2 which I can fill down so that the progressive sum accounts for the text at Column C. e.g. As C4="ABC", I do not want the progressive sum of B3 and A4 (13+9=22) to be displayed at D4. I need it to be at D3. The text "ABC" will appear randomly in Column C. Since both C6 and C7 both are "ABC", I need the sum of B5, A6 and A7 to be displayed at D5.

When I obtain Column D as per the table below, it would also be nice to have a formula at E2 which I can fill down so that I get the display without blanks like shown in the table.

ABCDE
1NumberProgressive
Number
CriteriaProgressive
(what I need)
Progressive
25555
38132222
4922ABC39
56283941
6432ABC
7739ABC
824141
9
10

<tbody>
</tbody>

Thanks.
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
How about

<b></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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Number</td><td style=";">Progressive Number</td><td style=";">Criteria</td><td style=";">Progressive (1)</td><td style=";">Progressive (2)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">8</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">22</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">22</td><td style=";">ABC</td><td style=";"></td><td style="text-align: right;;">39</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;">39</td><td style="text-align: right;;">41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">32</td><td style=";">ABC</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">39</td><td style=";">ABC</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">2</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;">41</td><td style=";"></td></tr></tbody></table><p style="width:4em;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)"></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>Array 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)">D2</th><td style="text-align:left">{=IF(<font color="Blue">C2<>"","",IF(<font color="Red">C3="",B2,INDEX(<font color="Green">B3:$B$9,COUNTIF(<font color="Purple">C3:INDIRECT(<font color="Teal">"C"&SMALL(<font color="#FF00FF">IF(<font color="Navy">C3:$C$9="",ROW(<font color="Blue">C3:$C$9</font>)</font>),1</font>)</font>),"ABC"</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$D$1:$D$8,SMALL(<font color="Green">IF(<font color="Purple">$D$2:$D$8<>"", ROW(<font color="Teal">$D$2:$D$8</font>)</font>), ROW(<font color="Purple"></font>)-1</font>)</font>),""</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 />

Capture the array formulas in D2 and E2 and copy down.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,301
Here are a couple non-array formulas that also work:

<b></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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Number</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Progressive Number</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Criteria</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Progressive (1)</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Progressive (2)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;">22</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">ABC</td><td style="border-left: 1px solid black;;"></td><td style="text-align: right;;">39</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">28</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;">39</td><td style="text-align: right;;">41</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">32</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">ABC</td><td style="border-left: 1px solid black;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">39</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">ABC</td><td style="border-left: 1px solid black;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">41</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-left: 1px solid black;;">41</td><td style=";"></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)">Sheet6</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)">D2</th><td style="text-align:left">=IF(<font color="Blue">C2<>"","",IF(<font color="Red">C3="",SUM(<font color="Green">A$2:A2</font>),SUM(<font color="Green">A$2:INDEX(<font color="Purple">A:A,AGGREGATE(<font color="Teal">15,6,ROW(<font color="#FF00FF">C3:C$10</font>)/(<font color="#FF00FF">C3:C$10=""</font>),1</font>)-1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IFERROR(<font color="Blue">SMALL(<font color="Red">D$2:D$10,ROWS(<font color="Green">$D$2:$D2</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,301
One caveat I should have mentioned. The E2 formula assumes that the values in A are all positive. If not, you'll need to go with Dante's formula. My D2 formula works fine either way.
 

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
155
Office Version
2016, 2011, 2007
Platform
Windows
Thanks to both of you gentlemen for such nice solutions!! It is always great to have multiple options.

Almost worked for me. 2 little issues here:

@DanteAmor, The table you have provided at #2 , at D5 I am expecting 39 but when I am using the formula in my sample I am getting 32. Probably that is because of the "HOLIDAY" repeating at consecutive rows and the C7 is somehow getting ignored.

Could you please make a little adjustment to:
Code:
[COLOR=#333333]{=IF([/COLOR][COLOR=Blue]C2<>"","",IF([COLOR=Red]C3="",B2,INDEX([COLOR=Green]B3:$B$9,COUNTIF([COLOR=Purple]C3:INDIRECT([COLOR=Teal]"C"&SMALL([COLOR=#FF00FF]IF([COLOR=Navy]C3:$C$9="",ROW([COLOR=Blue]C3:$C$9[/COLOR])[/COLOR]),1[/COLOR])[/COLOR]),"ABC"[/COLOR])[/COLOR])[/COLOR])[/COLOR][COLOR=#333333])}[/COLOR]
The formula for Column E works just fine.

@Eric W, Both of your formula worked for me. But unfortunately, the "AGGREGATE" function is giving errors to some of my users who are using older versions of excel (which it should). I do not have control on which version of excel is being used by my users.

Thanks again!
 
Last edited:

masud8956

Board Regular
Joined
Oct 22, 2016
Messages
155
Office Version
2016, 2011, 2007
Platform
Windows
@DanteAmor,

I am sorry. Your formula is fine. I messed it up a little.

No need for modification. Thanks!!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,301
Without AGGREGATE, you'll need to use Control+Shift+Enter, but here's how to adapt my D2 formula:

=IF(C2<>"","",IF(C3="",SUM(A$2:A2),SUM(A$2:INDEX(A:A,MIN(IF(C3:C$10="",ROW(C3:C$10)))-1))))

It still has the benefit of not being volatile.
 

Forum statistics

Threads
1,089,626
Messages
5,409,410
Members
403,262
Latest member
pcotton

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top