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.
Best 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
-
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
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!