MIN (Collect) with multiple cross reference criteria?

Diane Moore

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.

Genevieve P.

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

Genevieve P.

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

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

Genevieve P.

Wonderful! I'm glad I could help 🙂