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

lisalettieri

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?

Paul Newcome

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()))




Paul Newcome

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.

lisalettieri

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.

Paul Newcome

Try this instead:

=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 <> ""))

lisalettieri

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

Paul Newcome

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()))




lisalettieri

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

Paul Newcome

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.

lisalettieri

Thanks Paul. You are a genius at SmartSheet…

Paul Newcome

Happy to help. 👍️

lisalettieri

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

Paul Newcome

Which formula are you using?

lisalettieri

You answered this. Thanks.