sum qty if upcoming row is 0

[aqeelnokia99]

i have a problem
[TABLE="width: 236"]
<tbody>[TR]
[TD="align: center"]mades up[/TD]
[TD="align: center"]QTY[/TD]
[TD="align: center"]SUM Result[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]99[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]13[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

AS U SEE IF MADESUP UPCOMING ROW =0 OR EMPTHY ADD HIS QTY IN RE**** COLOMS

 

[James006]

Hello,

In cell C2, you can test following Array Formula:

=IF(A2=0,"",SUM(B2:OFFSET($B$1,IFERROR(MATCH(TRUE,(A3:$A$18)>0,0)+ROW()-1,SUMPRODUCT(MAX((ROW($A$1:$A$50))*($A$1:$A$50<>""))))-1,0)))

Hope this will help

 

[aqeelnokia99]

Wronge answer

 

[Yongle]

The formula was pointing at column A instead of column B

=IF(A2=0,"",SUM(B2:OFFSET($B$1,IFERROR(MATCH(TRUE,(A3:$A$18)>0,0)+ROW()-1,SUMPRODUCT(MAX((ROW($B$1:$B$50))*($B$1:$B$50<>""))))-1,0)))

 

[aqeelnokia99]

i past formula in colom c2 and get wrong answer whre i put it ?

 

[Yongle]

The formula as amended in post#4 works for me

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

mades up​

[/td][td]

QTY​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

50​

[/td][td]

1​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

0​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

0​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

7​

[/td][td]

4​

[/td][td]

10​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

0​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

3​

[/td][td]

3​

[/td][td]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

0​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

99​

[/td][td]

1​

[/td][td]

13​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

0​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

0​

[/td][td]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

55​

[/td][td]

2​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

0​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

55​

[/td][td]

2​

[/td][td]

20​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

0​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

0​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

0​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

0​

[/td][td]

7​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[Fluff]

The formula is an array formula & needs to be confirmed with Ctrl Shift Enter, not just enter

 

[aqeelnokia99]

with control +SHIFT + enter its work thanks u very much

 

[James006]

@Fluff

Thanks for pointing out how an Array Formula needs to confirmed ...