Progressive summing basing on cell value of another column

[masud8956]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Progressive
Number[/TD]
[TD="align: center"]Criteria[/TD]
[TD="align: center"]Progressive
(what I need)[/TD]
[TD="align: center"]Progressive[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"]39[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]41[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]ABC[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"][/TD]
[TD="align: center"]41[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks.

 

[DanteAmor]

How about

Book1
	A	B	C	D	E
1	Number	Progressive Number	Criteria	Progressive (1)	Progressive (2)
2	5	5		5	5
3	8	13		22	22
4	9	22	ABC		39
5	6	28		39	41
6	4	32	ABC
7	7	39	ABC
8	2	41		41
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IF(C2<>"","",IF(C3="",SUM(A$2:A2),SUM(A$2:INDEX(A:A,AGGREGATE(15,6,ROW(C3:C$10)/(C3:C$10=""),1)-1))))
E2:E8	E2	=IFERROR(SMALL(D$2:D$10,ROWS($D$2:$D2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

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

 

[Eric W]

Here are a couple non-array formulas that also work:

Book1
	A	B	C	D	E
1	Number	Progressive Number	Criteria	Progressive (1)	Progressive (2)
2	5	5		5	5
3	8	13		22	22
4	9	22	ABC		39
5	6	28		39	41
6	4	32	ABC
7	7	39	ABC
8	2	41		41
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IF(C2<>"","",IF(C3="",SUM(A$2:A2),SUM(A$2:INDEX(A:A,AGGREGATE(15,6,ROW(C3:C$10)/(C3:C$10=""),1)-1))))
E2:E8	E2	=IFERROR(SMALL(D$2:D$10,ROWS($D$2:$D2)),"")

 

[Eric W]

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]

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:

[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!

 

[masud8956]

@DanteAmor,

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

No need for modification. Thanks!!

 

[DanteAmor]

@DanteAmor,

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

No need for modification. Thanks!!

Hi @masud8956, I am glad to help you. Thanks for the feedback.

 

[Eric W]

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.

 

[masud8956]

Thank you @Eric W!

Formula is working nicely.