Stop Sum when Count Maximum is Reached

[Duncan20]

I am trying to achieve a formula that will only sum rows which have a count value of 1 and ignore 0 valued rows.

However I only want the sum of the previous 12 rows with a 1 count value (Column C).

For example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]110
[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]120[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]130[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]140[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]150[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]160[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]170[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]180[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD]190[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD]200[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A12[/TD]
[TD]210[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A13[/TD]
[TD]220[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A14[/TD]
[TD]230[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A15[/TD]
[TD]240[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A16[/TD]
[TD]250[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A17[/TD]
[TD]260[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A18[/TD]
[TD]270[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A19[/TD]
[TD]280[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A20[/TD]
[TD]290[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like cell D20 to sum B20+B19+B18+B17+B15+B14+B13+B12+B11+B10+B8+B7 excluding B16 & B9 as they have a zero value in Column C but to stop at B7 as I only want to sum the total of 12 cells.

There will be no regular pattern for the values in column C other than they will be 1 or 0.

Many thanks in advance

 

[shaowu459]

You post a similar question before, please try this,ctrl shift enter for the array formul:

=SUM(OFFSET(B20,,,-MATCH(12,COUNTIF(OFFSET(C20,,,-ROW(1:20),),">0"),),)*OFFSET(C20,,,-MATCH(12,COUNTIF(OFFSET(C20,,,-ROW(1:20),),">0"),),))

 

[Aladin Akyurek]

Not clear how the output must look like...

Either...

In D1 enter and copy down:

=IF(COUNTIFS($C$1:C1,1)=12,SUMIFS($B$1:B1,$C$1:C1,1),"")

Or, in a single cell, control+shift+enter, not just enter:

=SUM(IF(ROW($C$1:$C$20)<=SMALL(IF($C$1:$C$20=1,ROW($C$1:$C$20)),MIN(12,COUNTIFS($C$1:$C$20,1))),IF($C$1:$C$20=1,$B$1:$B$20)))

 

[wideboydixon]

I suspect I've massively overengineered this but since I did some work on it, here goes:

Book1
	A	B	C	D
1	A1	100	1	?
2	A2	110	1	?
3	A3	120	0	?
4	A4	130	1	?
5	A5	140	1	?
6	A6	150	1	?
7	A7	160	1	?
8	A8	170	1	?
9	A9	180	0	?
10	A10	190	1	?
11	A11	200	1	?
12	A12	210	1	?
13	A13	220	1	?
14	A14	230	1	2010
15	A15	240	1	2150
16	A16	250	0	2150
17	A17	260	1	2300
18	A18	270	1	2440
19	A19	280	1	2580
20	A20	290	1	2720
Sheet1

Cell Formulas
Range	Formula
D1	{=IFERROR(SUMIF(INDEX($C$1:$C1,MATCH(12,SUBTOTAL(9,OFFSET($C$1,ROW($C$1:$C1)-ROW($C$1),,ROW($C1)-ROW($C$1:$C1)+1)),0)):$C1,1,INDEX($B$1:$B1,MATCH(12,SUBTOTAL(9,OFFSET($C$1,ROW($C$1:$C1)-ROW($C$1),,ROW($C1)-ROW($C$1:$C1)+1)),0)):$B1),"?")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD