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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!