0

I have a table which looks like this:

1

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)

spreadsheet sample here

|improve this question
  • 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 at 14:21
  • 1
    @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)) – MARK MY ANSWER Jan 24 at 21:01
  • 2
    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 at 21:41
  • 1
    @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 at 4:07
  • 1
    @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 at 4:50
1

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

|improve this answer
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)

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.