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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!