LOOKUP OR FIND WITH SUMUP COMBINED
Hello,
Hope you could help me.
On Sheet 1, "Future Column", I would like to find in Sheet 2 all similar names against sheet 1.
Ex. In Sheet 1 - BOFA, BOFACBRE, BOFAJLL is on Sheet 2 all in Different rows. I would like to combine and sum up as for Sheet 1 "Future" is equal to 9.
SHEET 1
SHEET 2
Best Answers
-
You are going to want to use something along the lines of...
=SUMIFS({Sheet 2 Future Column}, {Sheet 2 XREFCS Column}, CONTAINS(@cell, [XREF/XREFS]@row))
-
Make sure you are using the same delimiter between each entry and try removing the spaces. It is working on my sheets.
Answers
-
You are going to want to use something along the lines of...
=SUMIFS({Sheet 2 Future Column}, {Sheet 2 XREFCS Column}, CONTAINS(@cell, [XREF/XREFS]@row))
-
THANK YOU. YOU'RE THE BEST PAUL !!!
-
Another question.. I'm having different result when Sheet 2 have a missing XREFCS. I have to manually remove in Sheet one the XREF/XREFS.
Sheet 1 is constant, will not change.
Thanks Again.
-
I'm not sure I understand exactly what you mean... Can you provide a screenshot example?
-
Hi Paul,
In sheet 2, we have BOFA, BOFACBRE and BOFAJLL, the sum in Future in Sheet 2 for those I mentioned is 9. However in ......
Drawing 1 : SUM is 16
Drawing 2 SUM IN 9 - CORRECT ONE BELOW. I removed the other Names in XREF/XREFS
-
Is it possible that those two additional names in sheet one are in another cell on sheet two? When I did my testing, it worked fine for me.
-
I checked there's no additional names.
When I removed BOFALHC, BOFACBREASCO, BOFACSH, BOFAMILLER and leave BOFA, BOFACBRE and BOFAJLL. The sum will be 9 which is correct.
Do you think I have to write the names one by one to get exact sum?
-
Make sure you are using the same delimiter between each entry and try removing the spaces. It is working on my sheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!