0

I found this prior answer to the issue here:

How to increment only if cell is not empty

But how to make it work on columns with IFNAs as blanks?

Demo:

https://i.stack.imgur.com/GGOr6.gif

Sample sheet:

Table:

1 Test ONE and only. 1 ONE 1 ONE
2 2
2 Test TWO and only. 3 TWO 3 TWO
4 4
3 Test THREE and only. 5 THREE 5 THREE
6 6
4 Test FOUR and only. 7 FOUR 7 FOUR
8 8
9 9
10 10
5 Test FIVE and only. 11 FIVE 11 FIVE
12 12

Formulas:

A1:B12:

=arrayformula( 
  iferror( 
    countifs(row(B1:B), "<=" & row(B1:B), B1:B, "<>") 
    / 
    not(isblank(B1:B)) 
  ) 
)

D1:D12:

=arrayformula( 
  iferror( 
    countifs(row(E1:E), "<=" & row(E1:E), E1:E, "<>") 
    / 
    not(isblank(E1:E)) 
  ) 
)

=IFNA(ArrayFormula(REGEXEXTRACT(B1:B,"\b([A-Z]{2,})+(?:\s+[A-Z]+)*\b")),"")

I tried solution with IFNA as below but it's not working.

G1:H12:

=IFNA(arrayformula( 
  iferror( 
    countifs(row(H1:H), "<=" & row(H1:H), H1:H, "<>") 
    / 
    not(isblank(H1:H)) 
  ) 
),"")

=IFNA(ArrayFormula(REGEXEXTRACT(E1:E,"\b([A-Z]{2,})+(?:\s+[A-Z]+)*\b")),"")

Your solution is greatly appreciated.

Lod
  • 657
  • 1
  • 9
  • 30
  • For later reference and ease of search as related questions: https://stackoverflow.com/questions/75345544/how-to-skip-down-by-1-row-cell-the-formula-output-and-remove-the-last-sequential – Lod Aug 08 '23 at 19:13

2 Answers2

1

Try this out:

=ARRAYFORMULA(IF(ISNA(E:E),,SCAN(,E:E,LAMBDA(a,c,a+NOT(ISNA(c))))))

Update

=ARRAYFORMULA(IFNA(IF(D:D="",,SCAN(,D:D,LAMBDA(a,c,a+(IFNA(c)<>""))))))
z''
  • 4,527
  • 2
  • 3
  • 12
  • 1
    Works great, but needs the input to be with visible `#N/A` (G:H) https://docs.google.com/spreadsheets/d/1dQIjiLjQ-t4EDohoVvwpmaFSTDRrtnXjromp9bUNwT4/edit#gid=1774121300 Any fix working with `#N/A` 's invisible? as in (D:E) Many Thanks! Be well! – Lod Feb 14 '23 at 15:26
  • Sorry for coming back late. I found another use case with regex that gives some troubles here: https://docs.google.com/spreadsheets/d/1dQIjiLjQ-t4EDohoVvwpmaFSTDRrtnXjromp9bUNwT4/edit#gid=42150428 I tried to update your formulas in cols D and E (input and logical checks in cols F, G, H. C is same attempt to no avail with @rochinfreakshow's formula). Any fix for cols D and E? The blank cells are not #N/A anymore but still not empty. All I could think of using is the the regex logical Check. Thanks! – Lod Feb 15 '23 at 01:00
  • 1
    what's the desired result? And could you give edit rights? – z'' Feb 15 '23 at 01:24
  • Sure, the expected result would be as before: simple incrementing by 1 at each "non-blank" cells. For the edits right I'm not sure because @TheMaster told me here https://stackoverflow.com/q/74234209/10789707 (1st comment) people could abuse the email for spam when you do it. And to use the SO table feature instead of Sample sheets to keep a data reproducible example in case the OP deletes the Sample sheets later here: https://webapps.stackexchange.com/questions/138382/how-to-share-a-google-sheets-demo-spreadsheet/161855#161855 Would it do making a local copy instead? Thank you. – Lod Feb 15 '23 at 12:54
1

You may try:

=index(if(H:H="",,scan(,H:H,lambda(a,c,if(c="",a,a+1)))))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • Works great as needed. @ztiaa works great as well but requires `#N/A` to be visible, while this one does not: https://docs.google.com/spreadsheets/d/1dQIjiLjQ-t4EDohoVvwpmaFSTDRrtnXjromp9bUNwT4/edit#gid=3778619 Prefered solution so far. I've notified @ztiaa and will see if the formula can be updated. Many Thanks! Be well! – Lod Feb 14 '23 at 15:27
  • @ztiaa's update works now. Yours is great too! Would tick it as well as accepted if it were possible. Thanks again @rockinfreakshow! – Lod Feb 14 '23 at 15:51
  • Sorry for coming back late. I found another use case with regex that gives some troubles here: https://docs.google.com/spreadsheets/d/1dQIjiLjQ-t4EDohoVvwpmaFSTDRrtnXjromp9bUNwT4/edit#gid=42150428 I tried to update your formula in cols C (input and logical checks in cols F, G, H. D, E is same attempt to no avail with @ztiaa's formulas). Any fix for col C? The blank cells in Col F are not #N/A anymore but still not empty. All I could think of using is the the regex logical Check. Thanks! – Lod Feb 15 '23 at 00:59
  • 1
    try: `=index(lambda(z,if(z="",,scan(,z,lambda(a,c,if(c="",a,a+1)))))(regexreplace(F:F,"^\s+$","")))` – rockinfreakshow Feb 15 '23 at 06:36
  • Great help, as always, works like a charm! Thank you, really appreciated. – Lod Feb 15 '23 at 12:45
  • I think I might make it a new question for ease of searching for others' benefit. Please let me know when you're online and I'll do it and accept your answer. – Lod Feb 15 '23 at 12:56