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
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Diane Moore
    Diane Moore ✭✭✭✭

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

  • Wonderful! I'm glad I could help 🙂

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!