MIN (Collect) with multiple cross reference criteria?

Due to the 500k cell limitation, I have to break my source data up into multiple files. I am attempting to write a formula that looks for the MIN date across date columns in two files when the row includes the customer name. This is what I have and it isn't working. Would love some help.

=MIN(COLLECT({sheet1-date}, {sheet1-customer}, Label1, {sheet2-date}, {sheet2-customer}, "customer name"))

Also, when I read the help document on COLLECT, it says all criteria must be met in order for SS to successfully collect the data. What would I do if the customer only appears in 1 of the 2 sheets? I am trying to set up a template and would like it to work if the customer is in one or both sheets?

Thanks in advance.

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Diane Moore

    You can't reference two different sheets within the same function - you'll need to write two separate MIN formulas, then wrap that in an overall MIN... try something like this:

    =MIN(Sheet 1 formula, Sheet 2 formula)

    =MIN(MIN(COLLECT({sheet1-date}, {sheet1-customer}, Label1)), MIN(COLLECT({sheet2-date}, {sheet2-customer}, "customer name")))

    However if the customer may only appear in one sheet, we'll need to first check that sheet to see if there aren any rows associated with that customer. If there aren't lets return a date wayyy in the future so that it's not counted as the MIN:

    =MIN(IF(COUNTIF({sheet1-customer}, "Label1") = 0, DATE(2050, 1, 1), MIN(COLLECT({sheet1-date}, {sheet1-customer}, Label1))), MIN(COLLECT({sheet2-date}, {sheet2-customer}, "customer name")))


    Let me know if this work for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Diane Moore

    You can't reference two different sheets within the same function - you'll need to write two separate MIN formulas, then wrap that in an overall MIN... try something like this:

    =MIN(Sheet 1 formula, Sheet 2 formula)

    =MIN(MIN(COLLECT({sheet1-date}, {sheet1-customer}, Label1)), MIN(COLLECT({sheet2-date}, {sheet2-customer}, "customer name")))

    However if the customer may only appear in one sheet, we'll need to first check that sheet to see if there aren any rows associated with that customer. If there aren't lets return a date wayyy in the future so that it's not counted as the MIN:

    =MIN(IF(COUNTIF({sheet1-customer}, "Label1") = 0, DATE(2050, 1, 1), MIN(COLLECT({sheet1-date}, {sheet1-customer}, Label1))), MIN(COLLECT({sheet2-date}, {sheet2-customer}, "customer name")))


    Let me know if this work for you!

    Cheers,

    Genevieve

  • Diane Moore
    Diane Moore ✭✭✭✭

    @Genevieve P. This worked perfectly and you did an excellent job explaining it. Thank you so much!

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad I could help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!