Sometimes you need to identify the last used cell in a column. The versatile SUMPRODUCT function can calculate that using a couple of other functions.

You can combine the MAX and ROW functions within the brackets of a SUMPRODUCT function to calculate the last used row in a column.

In Excel TRUE = 1 and FALSE = 0. Multiplying anything by one leaves it unchanged. Multiplying anything by zero makes it zero. This TRUE and FALSE multiplication forms the basis of how SUMPRODUCT can identify the last used cell. This SUMPRODUCT technique is not included in Excel’s help system.

The formula for the last used cell in column A is

=SUMPRODUCT(MAX(ROW(A:A)*(A:A<>"")))

Cell C1 has the formula. There are no other entries below the image.

Formula explanation

ROW(A:A) provides the row number of every row in the column.

(A:A<>””) will return TRUE for each cell in column A that isn’t blank. The <> symbols means not equal to. “” is the shorthand way to refer to a blank cell. It is in effect saying all cells in column A not equal to blank.

The results of the ROW and the blank comparison are multiplied together. This will return all the row numbers for each non-blank cell in column A.

The MAX function then extracts the highest row number from that list and the SUMPRODUCT returns it.

The brackets around the expressions are important as they isolate the calculations and make they are calculated before the multiplication is performed. The TRUE and FALSE results are converted to 1 and 0 when the bracketed expressions are multiplied together.

You have to use the SUMPRODUCT function because it can work with arrays – most functions can’t. An array is a special type of list.

If you try

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

It would return 1 in this example because it only examines the first cell. When used inside the SUMPRODUCT brackets it works with the whole range.

I’ll discuss arrays more in future posts.

The above SUMPRODUCT formula works in Excel 2010 onwards. If you are using it in Excel 2007 or 2003 then you need to limit the range because SUMPRODUCT can’t refer to a whole column in those versions. Instead you would use something like

=SUMPRODUCT(MAX(ROW(A1:A50000)*(A1:A50000<>"")))

You must make sure that the two ranges use the same start and end row numbers.