CODEWITHSUNDEEP
google-sheets
Marcelo
Marcelo

Reputation: 1196

Count values until the first empty cell

What I looking for is how to count as many values in column, but I want it to stop counting as soon as it hits the first empty cell. I am trying to do it without using app script.

Example:

1
2
312
EMPTY
3123

Should return 3, if I simply use COUNTA(), it will return 4.

Any ideas?

Upvotes: 8

Views: 12445

Answers (1)

user3616725
user3616725

Reputation: 3655

If your "empty" cells are indeed BLANK then you can use the following:
=ArrayFormula(match(TRUE,ISBLANK(A1:A13),0)-1)
(as long as there is always an empty row between the sets of "Years"

  • the ISBLANK(A1:A13) returns an array result {FALSE,FALSE,FALSE,TRUE,FALSE,...}
  • the match() returns the POSITION or ROW of the first TRUE in that list : 4
  • we then take away 1, for the empty row
  • we have to run the whole thing as an array formula because we need ISBLANK() to work on each cell in turn.

if they contain text "EMPTY" then use:
=ArrayFormula(match(TRUE,if(A1:A13="EMPTY",TRUE,FALSE),0)-1)

Upvotes: 15

Related Questions