I am trying to find the column number of the last non-zero cell in a range. I have tried the following formulas:

=MAX((O8:BA8<>0)*(COLUMN(O8:BA8)))

=SUMPRODUCT(MAX((O8:BA8<>0)*COLUMN(O8:BA8)))

But for both of these, although my range is only 39 values wide... (from column O to BA), it keeps giving me a return value of 45, which cannot be the answer since it has to be less than 39, and the answer actually is 31.

The below snapshot is a sample of the rows that I need to do this for. It is a mix of 0s and numbers greater than 0 (hours logged). Each row begins and stops the hours logged on different dates, so this is why I am trying to get the column number - so I can use this to look up the corresponding dates for the last hours logged by row. If you have any other suggestions for this end goal, I would greatly appreciate it also. Otherwise, I would love to know if anybody has a way to get the correct column number!

Thanks in advance!

CodePudding user response：

It's quite evident from your description that your title is misleading, i.e. you are not in fact looking to return the "column number", but rather the relative position within the range O8:BA8, which can be achieved simply via:

=MATCH(1,0/O8:BA8)

Note that this will error if all entries in the range are zero.