Find the last row with zero value in a range using formula

0
votes

I'm currently looking for a formula to return the no. of the row where I can find the last zero value in a 1-column range.

Example :

  • 0
  • 0
  • 0
  • 28
  • 28
  • 28
  • 28
  • 0
  • 28

Considering rows above, I would like to find a formula that would return 8 (no. of the row) because it's the last row with a zero value.

2
excelgoogle-sheetsgoogle-sheets-formula

2 Answers

2
votes

Try this:

=MAX((A:A<>"")*(A:A=0)*ROW(A:A))
0
votes

try:

=INDEX(MAX(IF((A:A=0)*(A:A<>""); ROW(A:A); )))

enter image description here