# Help Understanding "Return Last Non-Blank Row #" Formula

1. ## Help Understanding "Return Last Non-Blank Row #" Formula

Hello, I came across a formula that is built to return the last non-blank row number in a range.

The formula is
Formula:  `Please Login or Register  to view this content.`

I am having trouble understanding how it works.

I understand that ROW(A2:A20) will return the value "2".

I understand that A2:A20<>"" SHOULD* return "TRUE" if there is at least one non-blank cell, and "FALSE" if all cells in the range are blank. (*See below).

I do not understand how TRUE * 2 or FALSE * 2 works, or how inserting that into a MAX function works. Isn't it only trying to max 1 singular number?

Lastly, I don't understand how the SUMPRODUCT formula brings this all together.

*Checking the "range not equal to blank" portion of the formula, I ran into the following issue. Any small-scale test I did yielded the expected results. However, I am trying to check an entire column, down to the end of the sheet, so there are many blank cells. Even though I have 65 non-blank cells, the function for some reason returns "FALSE" for when I check the entire range.

My formula is =H70:H1048576<>"" and returns "FALSE" even through H71:H135 are all filled with values.  Register To Reply

2. ## Re: Help Understanding "Return Last Non-Blank Row #" Formula

The Sumproduct function allows you to use an array formula without using ctrl shift enter.

So the formula (A2:A20<>"")*ROW(A2:A20)

Will return a list of zeros and row numbers

The zeros corresponding to blank rows, the row numbers for the non blank rows

So the max function will return the row number of the last non blank row

The sumproduct allows this to work.

if you highlight (A2:A20<>"")*ROW(A2:A20) in the formula bar and press f9 you will see the data before the max function gets to work on it.  Register To Reply

3. ## Re: Help Understanding "Return Last Non-Blank Row #" Formula Originally Posted by mehmetcik So the formula (A2:A20<>"")*ROW(A2:A20)

Will return a list of zeros and row numbers
Hmm, alright, this is starting to make a little more sense... Is there any way of understanding why this formula returns specifically a list of numbers? Because also if you put =(A2:A20<>"")*ROW(A2:A20) in a cell, it just comes back with 0.

Thanks for mentioning the F9 trick, didn't know about that.  Register To Reply

4. ## Re: Help Understanding "Return Last Non-Blank Row #" Formula

paste =(A2:A20<>"")*ROW(A2:A20) into a cell

Select the cell and enter it using Ctrl Shift Enter

You may well see a 0.

select (A2:A20<>"")*ROW(A2:A20) and press f9

you will see a string of numbers.

=max((A2:A20<>"")*ROW(A2:A20) ) and enter it using Ctrl Shift Enter.

ok?  Register To Reply

5. ## Re: Help Understanding "Return Last Non-Blank Row #" Formula

=lookup(1,0/(a1:a50<>""),row(a1:a50))

or

=sumproduct((row(a1:a50)= max(if(a1:a50<>"",row(a1:a50))))*(row(a1:a50)))  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 