Formula to Find Last Non-Empty Cell In a Row

Michael Hatchell · 10/26/20

I am using a sheet that tracks counts of different products by location, which gets updated in 30-minute intervals (product location by row and time intervals by column). My goal is to use a function to automatically pull the most recent count for each product, which would require the function to check for the last non-empty cell in a given row and count that number.

I've found a function that appears to work just fine in Excel/Google Sheets, but always comes up as #UNPARSEABLE when used in Smartsheet. Formula, below, links to the original page that I found it on which explains how the formula works.

=LOOKUP(2,1/(A:A<>""),A:A)

Does anyone know how to help me make this work?? Does Smartsheet not have the capability to do anything like this?

Thank you in advance!!

Jason Cobb · 03/13/21

Oh do I have a solution for you! Newbie here, but lmk what I can do to help. There's always an easier solution.

=IF( [SUM of DATA in ROW] > [other DATA in ROW], [SUM of DATA in ROW] )

Check out the brown row. It has 53 columns. (I have the sums reaching forward and back a bit, referencing other cells) The formula has the range my data is in, a ">" operator (condition), and the same data range PLUS 1. IF this condition is met the sum is posted, if it is not, it is blank.

Sean Morgan · 10/30/20

Hi there @Michael Hatchell ,

I feel that based on the Screenshot above, and the original intended use of the Excel Formula, I may have found the Formula you are looking for:

=INDEX([Primary Column]:[Primary Column], COUNTIFS([Primary Column]:[Primary Column], NOT(ISBLANK(@cell))))

From my Screenshot, you can see that in the Primary Column, the last number entered is 20, and when using the Formula above, it has returned the latest entry in that Column.

Although this Formula may seem larger than it's Excel variant, and although there may be a more simplified Formula for the same purpose, this should provide the result you are looking for.

Here are the Articles I used to create this for if you would like a detailed breakdown on how they can be used:

INDEX: https://help.smartsheet.com/function/index

COUNTIFS: https://help.smartsheet.com/function/countifs

NOT: https://help.smartsheet.com/function/not

ISBLANK: https://help.smartsheet.com/function/isblank

@Cell: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Let me know if you have any questions!

Regards

Sean

Jason Cobb · 03/13/21

Oh do I have a solution for you! Newbie here, but lmk what I can do to help. There's always an easier solution.

=IF( [SUM of DATA in ROW] > [other DATA in ROW], [SUM of DATA in ROW] )

Check out the brown row. It has 53 columns. (I have the sums reaching forward and back a bit, referencing other cells) The formula has the range my data is in, a ">" operator (condition), and the same data range PLUS 1. IF this condition is met the sum is posted, if it is not, it is blank.

ChadMartz_44 · 08/25/22

https://community.smartsheet.com/discussion/comment/267553#Comment_267553

@Sean Morgan

I have something similar and your formula works. Expect it say cell value 23 is blank. 20 will still want to be returned.

Formula to Find Last Non-Empty Cell In a Row

Best Answer

Answers

Help Article Resources

Categories