ArrayFormula for conditional consequential sum-up?

Question

I have a table which looks like this:

And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula.

I would like to know how to create ArrayFormula variant of this formula:

=IF(A3>5, B2, B2+1)

Allen, in the meantime, your sample sheet is "View Only." Please change the permissions to "Anyone with the link can Edit." — Erik Tyler, Jan 24, 2019 at 14:21
@AllenSierra LEN counts how many characters are in cell. =ARRAYFORMULA(IF(LEN(A1:A), true, false)) checks every row in given range if its empty/not-empty. in other words this works like some check-in restriction. kinda same as =ARRAYFORMULA(IF(A1:A<>"", true, false)) — user0, Jan 24, 2019 at 21:01
As a fail safe, I've gone ahead and uploaded a formula into your sample sheet, Sheet2!B1: =ArrayFormula({"Result";IF(A2:A="","",COUNTIFS(ROW(A2:A),"<="&ROW(A2:A),A2:A,"<=5"))}) — Erik Tyler, Jan 24, 2019 at 21:41
@Allen, my formula results in a match to the image you posted in the original post. I'll wait for a reply to this point before I explain the formula. — Erik Tyler, Jan 26, 2019 at 4:07
@Allen, in addition to my formula producing results that match the image in your original post, the formula in your original post says (in English), "If the value in A is greater than five, don't add anything to the previous value; otherwise, add one." — Erik Tyler, Jan 26, 2019 at 4:50

Erik Tyler · Accepted Answer · 2019-01-26 03:53:24Z

1

=ArrayFormula({"Result";IF(A2:A="","",COUNTIFS(ROW(A2:A),"<="&ROW(A2:A),A2:A,"<=5"))})

answered Jan 26, 2019 at 3:53

Erik Tyler

4,4772 gold badges5 silver badges10 bronze badges

Add a comment |

user0 · Accepted Answer · 2019-01-28 19:31:08Z

0

probably not the answer OP is looking for, but here are some examples how to construct ARRAYFORMULA from =IF(A3>5, B2, B2+1)

answered Jan 25, 2019 at 2:25

user0

24.5k8 gold badges44 silver badges71 bronze badges

Add a comment |

2 Answers 2