sum qty if upcoming row is 0

[aqeelnokia99]

i have a problem

mades up	QTY	SUM Result
50	1	6
0	2
0	3
7	4	10
0	6
3	3	5
0	2
99	1	13
0	5
0	7
55	2	6
0	4
55	2	20
0	1
0	4
0	6
0	7

<tbody>
</tbody>

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

	A​	B​	C​
1​	mades up​	QTY​
2​	50​	1​	6​
3​	0​	2​
4​	0​	3​
5​	7​	4​	10​
6​	0​	6​
7​	3​	3​	5​
8​	0​	2​
9​	99​	1​	13​
10​	0​	5​
11​	0​	7​
12​	55​	2​	6​
13​	0​	4​
14​	55​	2​	20​
15​	0​	1​
16​	0​	4​
17​	0​	6​
18​	0​	7​

Sheet: Sheet3

 

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