Return the last number (current month) from a column of numbers
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?
Best Answers
-
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()))
-
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.
Answers
-
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.
-
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 <> ""))
-
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
-
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()))
-
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
-
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.
-
Thanks Paul. You are a genius at SmartSheet…
-
Happy to help. 👍️
-
Paul, can we add a phrase to make it return a blank if nothing in the "" cell? It's returning 0's. Thanks again!
-
Which formula are you using?
-
You answered this. Thanks.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives