# Return the last number (current month) from a column of numbers

Options
edited 06/21/24

I have a column of numbers, one for each month. I need my metrics form to return only the latest month's number (YTD). Can you advise?

• ✭✭✭✭✭✭
Options

My last formula will pull in the last filled in cell. So if the current month's row is blank, it will pull in the previous month. If you want to pull in the current month's regardless of whether or not it is blank, you would use:

=INDEX({FP 2024 Calls - by month Total SL%}, MONTH(TODAY()))

• ✭✭✭✭✭✭
Options

We first use the COLLECT function to pull all non-blank cells into a list. Then we INDEX that list. The COUNTIFS counts how many cells in the range are not blank. Using this number, we can dynamically tell the INDEX function to pull the last value from the list of non-blanks pulled together by the COLLECT function.

• Options

I also tried below to collect the number in 1st field if the 2nd field = current month.

=INDEX({FP 2024 Calls - by month Total SL%}, {FP 2024 Calls - by month Dates}, MONTH@cell), =MONTH(TODAY())) but getting UNPARSEABLE.

• ✭✭✭✭✭✭
Options

=INDEX(COLLECT({FP 2024 Calls - by month Total SL%}, {FP 2024 Calls - by month Total SL%}, @cell <> ""), COUNTIFS({FP 2024 Calls - by month Total SL%}, @cell <> ""))

• edited 06/21/24
Options

Hi Paul, I don't think this gives me what I need if I'm interpreting it correctly. I asked the wrong way so let me try again.

I need the formula to return the last number (for current month only) in the far right column {YTD Total SL%}. It needs to update each month automatically and reference a separate sheet. I put the formulas on a "Metrics" sheet and the data comes from above.

Thanks, Lisa

• ✭✭✭✭✭✭
Options

My last formula will pull in the last filled in cell. So if the current month's row is blank, it will pull in the previous month. If you want to pull in the current month's regardless of whether or not it is blank, you would use:

=INDEX({FP 2024 Calls - by month Total SL%}, MONTH(TODAY()))

• edited 06/21/24
Options

That worked thanks!

Why is there a COUNTIFS? Is that to make sure the columns after are blank? I'm trying to interpret your formula so I can apply elsewhere. Thanks, Paul

• ✭✭✭✭✭✭
Options

We first use the COLLECT function to pull all non-blank cells into a list. Then we INDEX that list. The COUNTIFS counts how many cells in the range are not blank. Using this number, we can dynamically tell the INDEX function to pull the last value from the list of non-blanks pulled together by the COLLECT function.

• Options

Thanks Paul. You are a genius at SmartSheet…

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

Paul, can we add a phrase to make it return a blank if nothing in the "" cell? It's returning 0's. Thanks again!

• ✭✭✭✭✭✭
Options

Which formula are you using?

• Options