INDEX/Collect function with two criteria returns #invalid value sometimes...

Options
James Kosmas
James Kosmas ✭✭
edited 03/27/22 in Formulas and Functions

Hi There,

I have a status update sheet that has many columns. The three relevant here are:

1) Date Column = {createdDate}

2) Checkbox = {PSR}

3) free text column = {Overall Commentary}

-----

In another sheet I am trying to collect the row with both the latest date and has the checkbox ticked.

Here is my formula:

=INDEX(COLLECT({Overall Commentary}, {createdDate}, MAX({createdDate}), {PSR}, 1), 1)

----

It seems to only work if one of the checkbox rows IS the max date. If there is a higher date that does not have a checkbox ticked it returns the error. I have double checked all my references and have tested it with dummy data and with one criteria at a time and always get the same results, which is it will work if one of the rows with a ticked checkbox is also the highest date. I have also reversed the formula to: =INDEX(COLLECT({Overall Commentary}, {PSR}, 1, {createdDate}, MAX({createdDate})), 1) and get the same results. Any help would be greatly appreciated.

----

Edit: I need to filter rows to only those that have the checkbox column ticked, then select MAX date from this filtered list. Seems like only the max date is used as the "filter" and just throws error if checkbox is not ticked, I need it to be the other way around.

Thanks!

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @James Kosmas

    I understand the formula throws an error when you don't have data that matches your criteria. As your formula is written, you will only have results, as you have experienced, when the max date is within your checked rows.

    A helper column (a checkbox will work) in your source sheet will help you accomplish your task. It is checked when the Created@row is the max date where the row has has PSR checked. Be sure to change the column names in the formula to reflect your actual column names.

    [Max Date]

    =IF(Created@row = MAX(COLLECT(Created:Created, [your source sheet PSR column]:[your source sheet PSR column], 1)), 1)

    Then your desired formula in your status update sheet becomes

    =INDEX(COLLECT({Overall Commentary}, {Source Sheet Max Date}, 1, {PSR}, 1), 1)

    You will need to manually insert the new {Max Date} cross-sheet reference into your sheet.

    Does this work for you?

    Kelly

  • James Kosmas
    Options

    Hi @Kelly Moore

    Thanks so much for your response.

    It almost works...

    =IF(Created@row = MAX(COLLECT(Created:Created, [your source sheet PSR column]:[your source sheet PSR column], 1)), 1)

    this will find the row with the highest date of all the ticked rows (even if there is a higher date that is not ticked). This is fantastic, but when it does this the helper row also selects any rows that have that same date (even if they are not ticked).

    Screenshot attached for reference.

    Thanks again!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey James

    Sorry about that. Try this

    =IF(AND(PSR@row=1,Created@row = MAX(COLLECT(Created:Created, PSR:PSR, 1))), 1)

    Kelly

  • James Kosmas
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!