Combining an IF / MIN Formula
I am using a COUNT IF / MIN formula to pull in the next upcoming date based on a specific name within a range, but the formula I have is not working. Here is the situation:
Sheet 1: A sheet for an individual client's open cases.
Sheet 2: A sheet that includes milestone dates for ALL clients.
I would like the ability to pull in the next upcoming date from {Sheet 2} based on if it matches the client's name from {Sheet 1}
Here is the formula I'm using in Sheet 1:
=IF(COUNTIFS({Sheet 2}, "Client Name") > 0, MIN(COLLECT({Sheet 2 Date Range}, {Sheet 2 Date Range}, @cell > [Effective Date]@row)))
This formula is pulling in a date, but it's just pulling in the FIRST upcoming date within the sheet, it's not looking to the client when it's further down in the sheet. Hope this makes sense! Would love some help on how to tweak this formula to make it pull in the correct date.
Best Answer
-
Hey @Kelly Pratt
As written above, the IF statement is only looking if the client name is in your data set. The client's name isn't part of the criteria for the collect. Add the range, client name to the Collect.
Did that work for you?
Kelly
Answers
-
Hey @Kelly Pratt
As written above, the IF statement is only looking if the client name is in your data set. The client's name isn't part of the criteria for the collect. Add the range, client name to the Collect.
Did that work for you?
Kelly
-
Thank you, Kelly! That worked! Here is my new formula:
=MIN(COLLECT({Sheet 2 Date Range}, {Sheet 2}, [Client Name]@row, {Sheet 2 Date Range}, @cell > [Effective Date]@row))
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!