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

  • Eric Law
    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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!