Obtain Most Recent Value based on date of within Month
Hello,
I am trying to obtain the most recent membership entered for the month, where the value doesn't matter if largest. The formula will be used to calculate the most recent membership of the month, for the calendar year from the Report Date.
I was able to use =MAX(COLLECT({Membership}, {Month}, "January")) but comes out to 300 and not 275. I can't seem to figure out the formula needed to modify.
{Membership}= Membership Value
{Month}=Month Helper Column
Answers
-
Try somethign like this instead:
=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January")), {Report Date}, 0))
-
Thank you, for some reason it's coming back blank. I have another sheet where I am posting the formula.
Any Clue? and Appreciate the assistance.
-
Are you able to provide a screenshot of the source sheet filtered to only show rows where the Month column is "January"?
-
Ok i see what happened, i had test entry for 2024 that had blank membership data, which when i removed it got the right information!
I tried to add a secondary criterion to consider the year as below but got an invalid operation.
=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January", {Report Date}, {Helper Year}, @cell = "2023")), {Report Date}, 0))
Any advice and appreciate the assistance.
-
Your syntax is off. Try this instead:
=INDEX({Membership}, MATCH(MAX(COLLECT({Report Date}, {Month}, @cell = "January", {Helper Year}, @cell = "2023")), {Report Date}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!