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
-
Paul Newcome ✭✭✭✭✭✭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))
-
Paul Newcome ✭✭✭✭✭✭
Make sure you are using the same delimiter between each entry and try removing the spaces. It is working on my sheets.
Answers
-
Paul Newcome ✭✭✭✭✭✭
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))
-
Marilen.Navarro103391 ✭✭✭✭✭THANK YOU. YOU'RE THE BEST PAUL !!!
-
Marilen.Navarro103391 ✭✭✭✭✭
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.
-
Paul Newcome ✭✭✭✭✭✭
I'm not sure I understand exactly what you mean... Can you provide a screenshot example?
-
Marilen.Navarro103391 ✭✭✭✭✭
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
-
Paul Newcome ✭✭✭✭✭✭
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.
-
Marilen.Navarro103391 ✭✭✭✭✭
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?
-
Paul Newcome ✭✭✭✭✭✭
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
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/69134/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":36,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
