Google Sheet ARRAYFORMULA with IF

0
votes

I am using IF function as below and getting the result, but when I use the same function with ARRAYFORMULA am getting the circulating dependency error:

Without ARRAYFORMULA =if(isblank(A2),"",IFERROR(IF(A2<>A2,1,B1+1) ))

With ARRAYFORMULA =ARRAYFORMULA(IF(A2:A<>A2:A,1,C1:C+1) )

Further, the result of formula is getting started from cell No. B3, is it possible to get the result started from cell B2 i.e. the cell in which the formula is applied.

Below is the link of the sheet:

https://docs.google.com/spreadsheets/d/1qLjYukNR1E_vgwqeGutQRtg3H7-3eU2heUPHsqQpg90/edit#gid=1713558709.

Any help on above will be greatly appreciated.

1
google-sheetsgoogle-sheets-formula
Maybe you should consider explaining what is your goal: What I have (input) VS what I want (output), and most likely a complete different approach will be suggested to you - Nabnub
Best Answer.... - Edyphant

1 Answers

2
votes

I have added a new sheet ("Erik Help") to your sample spreadsheet. This array formula can be found in B1 of that sheet:

=ArrayFormula({"Header"; IF(A2:A="",, ROW(A2:A)-VLOOKUP(ROW(A2:A),{1;FILTER(ROW(A2:A),A2:A="")},1,TRUE))})

You can change the header text as you like.

Here is a plain-English interpretation of the rest of the formula: "If a cell in A2:A is blank, leave it empty; otherwise, subtract the row number of the last empty row from the current non-blank row number. If A2 is non-blank, return 1."