Error Returning Result Using COLLECT with MAX Date Criteria

bill256

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?

Eric Law

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.

Paul Newcome

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.