CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Edyphant
Edyphant

Reputation: 175

Google Sheet ARRAYFORMULA with IF

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.

Upvotes: 0

Views: 710

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

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

Upvotes: 2

Related Questions