Trouble shooting a INDEX/MAX/Collect formula with an IF statement

So this is the weirdest thing - at least for me. The same formula works great for one column, but not for the other.

It works fantastic here: =IF(COUNTIFS({DAX Ambient Inventory Range Install Date}, ISDATE(@cell), {DAX Ambient Inventory Project #}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({DAX Ambient Inventory Range Install Date}, {DAX Ambient Inventory Range Install Date}, ISDATE(@cell), {DAX Ambient Inventory Project #}, [Project Number]@row)), 1)) 

But when I use the same formula here, it only provides data for the projects, that have no project number.

=IF(COUNTIFS({DAX iOS iPod Touch Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPod Touch Inventory Project Number}, [Project Number]@row) > 0, INDEX(MAX(COLLECT({DAX iOS iPod Touch Inventory Ship Date}, {DAX iOS iPod Touch Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPod Touch Inventory Project Number}, [Project Number]@row)), 1)) 

What could be the issue? Any suggestions on the steps to trouble shoot?

Thanks so much for sharing your advice.

Best regards, Barbara

Best Answer

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

    Hey @Barbara Witt

    That is strange. It doesn't make sense.

    You could delete all your cross sheet references and build them again, to be sure there isn't an inadvertent error. I would do this regardless. Start from scratch. Especially the range that references your project numbers since that seems to be the problem- start there.

    You could add in a known project number "xxx1234" instead of using the [Project Number]@row to see if it can find that specific instance.

    You could see if you could force the formula to avoid blank project names by adding additional criteria to your existing formula: {DAX iOS iPod Touch Inventory Project Number},@cell<>""

    SK will probably be able to offer additional help.

    -Kelly

Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Can you please elaborate on when you say does not work? You are getting error, no data, something else?

  • @SK It's complicated, I know....

    The first formula returns the MAX date for the projects with a project number. The second formula returns a date for the projects without a project number.

    These are the same formulas, thus I would think they should behave the same...It's strange.

    Thanks for your help. Best regards, Barbara

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

    Hey @Barbara Witt

    That is strange. It doesn't make sense.

    You could delete all your cross sheet references and build them again, to be sure there isn't an inadvertent error. I would do this regardless. Start from scratch. Especially the range that references your project numbers since that seems to be the problem- start there.

    You could add in a known project number "xxx1234" instead of using the [Project Number]@row to see if it can find that specific instance.

    You could see if you could force the formula to avoid blank project names by adding additional criteria to your existing formula: {DAX iOS iPod Touch Inventory Project Number},@cell<>""

    SK will probably be able to offer additional help.

    -Kelly

  • Hello @KDM and @SK

    Still strange, but that fixed it, Kelly. I added the additional criteria of making sure the Project Number is not empty. I only added it to the COLLECT portion of the formula, not the IF portion of the formula.

    Still don't understand why I don't have to do it with the other scenario, but that is ok. It's working now and that is the most important.

    As always, thank so much for your assistance.

    =IF(COUNTIFS({DAX iOS iPhone Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPhone Inventory Project Number}, [Project Number]@row)>0, INDEX(MAX(COLLECT(({DAX iOS iPhone Inventory Ship Date}, {DAX iOS iPhone Inventory Ship Date}, ISDATE(@cell), {DAX iOS iPhone Inventory Project Number}, [Project Number]@row, {DAX iOS iPhone Inventory Project Number}, @cell<>"")),1))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!