Collect Criteria not being filtered

edited 12/09/19 in Formulas and Functions

My nested COLLECT function's Criteria does not appear to be working because the formula runs into errors when a duplicate date is found before the actual date that should be being pulled. 


=INDEX([Baseline Variance]5:[Baseline Variance]128, MATCH(MAX(COLLECT([Task Complete Actual]5:[Task Complete Actual]128, [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker")), [Task Complete Actual]5:[Task Complete Actual]128, 0))


The [Milestone Type Tracker]5:[Milestone Type Tracker]128, ="Tracker" should filter out the row that has an identical Task Complete Actual date because it does not have the "Tracker" criteria.


Excel example attached

Yellow cell should read "4" but doesn't because the duplicate dates in Actual Complete column for Tasks 6 & 7

Schedule Smartsheet.PNG


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!