Error Returning Result Using COLLECT with MAX Date Criteria
I have 5 source tables where raw data is added to monthly (Titled Phase 0, Phase 1, etc). 4 rows of data are added each month to each source table, one for each Results Category option of Success, In Progress, Error, Unknown. One of my source tables is shown here
Separately, I have a summary sheet, where I would like to summarize only the most recent month's data in a simple table, as shown below:
In this screenshot I'm trying to grab the most recent value from the Count column in the source sheet where the Results Category = "Success", but I keep getting a Nested Criteria error.
The formula I am using is a collect formula, which is:
=COLLECT({Phase 0 Patching Results Range 2}, {Phase 0 Patching Results Range 3}, "Success", {Phase 0 Patching Results Range 4}, MAX(COLLECT({Phase 0 Patching Results Range 4}, {Phase 0 Patching Results Range 4}, ISDATE(@cell))))
I'm trying to use two criteria to select the right row:
- Results Category = Success
- Date is the MAX date found in the month column
I'm lost at this point. Can anyone point me in the right direction?
Answers
-
Hi @bill256
Firstly, Collect has to be used with another function, so this won't work. Have you tried SUMIFS?
I don't really know what you are trying to add up, but hopefully this will help. If you could give examples of what you would like it to do, it would be easier to assist.
-
It looks like the newest entries are being made at the top of the sheet. If that is the case, then you can just use an INDEX/MATCH like so:
=INDEX({Count}, MATCH("Success", {Results Category}, 0))
When referencing individual columns for each range, it is going to read the ranges from to to bottom and stop on the first match. So if the top match is the most recent match, that's all you really need to do.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!