Join formula of two sheets
你好,
Shown is one sheet but two sources separate data . Can I combine the two counts anyway they have the same Primary column. I don't want to make a summary count of the two because I can't get rid of this in reporting.
Can you assist me with the formula? Thanks.
Best Answer
-
Ramzi ✭✭✭✭
Here's another option:
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section.
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.
I hope that makes sense.
内容的解决方案架构师
www.adapture.com
Answers
-
Ramzi ✭✭✭✭
If I understand you correctly, you can just add the two formulas together to get the total.
内容的解决方案架构师
www.adapture.com
-
Marilen.Navarro103391 ✭✭✭✭✭hello. yes I can add the two formula but when I make a Report, it will show these two tables. Is there a formula where I can combine the sum of two sheets?
-
Ramzi ✭✭✭✭
You could potentially use the Group/Summarize features in the report. Will that work?
内容的解决方案架构师
www.adapture.com
-
Marilen.Navarro103391 ✭✭✭✭✭
@Ramziso it means I wouldn't be able to join a count of two sheets in table form?
-
Ramzi ✭✭✭✭
Here's another option:
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section.
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.
I hope that makes sense.
内容的解决方案架构师
www.adapture.com
-
Marilen.Navarro103391 ✭✭✭✭✭
@RamziHelper is good. I got it. Thanks for your help
-
Marilen.Navarro103391 ✭✭✭✭✭
@RamziI have another question, if you can help me set up with weekly count, starting September. I only know monthly, I haven't done weekly. Can you give me an idea? Thanks
Help Article Resources
Categories
Check out theFormula Handbook template!
=IF(OR([% Complete]@row = 1, [End Date]@row> TODAY(7)), \"Green\", IF([End Date]@row< TODAY(), \"Red\", \"Yellow\"))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108954,"type":"question","name":"COUNTIFS formula looking at date columns in a reference sheet","excerpt":"I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look at the number of violations per month. I also want to see the number of date violations that occurred per month. Date violations would be…","snippet":"I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look…","categoryID":322,"dateInserted":"2023-08-15T12:57:30+00:00","dateUpdated":null,"dateLastComment":"2023-08-15T19:04:51+00:00","insertUserID":161890,"insertUser":{"userID":161890,"name":"Jade Boring","title":"Project Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Jade%20Boring","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-15T20:08:59+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-15T20:43:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3384232341,"url":"https:\/\/community.smartsheet.com\/discussion\/108954\/countifs-formula-looking-at-date-columns-in-a-reference-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108954\/countifs-formula-looking-at-date-columns-in-a-reference-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108954,"commentID":390729,"name":"Re: COUNTIFS formula looking at date columns in a reference sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390729#Comment_390729","dateInserted":"2023-08-15T19:04:51+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-15T20:43:49+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/37DZUYMBZBUD\/inspection-metrics-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Inspection metrics.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-15T20:10:04+00:00","dateAnswered":"2023-08-15T13:04:02+00:00","acceptedAnswers":[{"commentID":390649,"body":"
You will need a helper column in the source sheet to indicate on each row where there is an issue then count (or sum depending on your indicator) how many rows are flagged in this helper column.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108950,"type":"question","name":"vLOOKUP SOMETIMES RETURNS NO MATCH","excerpt":"Hi Everyone, Thanks to some very helpful advice on here I've amended our company quotations sheet to reference a credit checking sheet and then show if a client has been credit checked and what their limit is. 99% of the time this works perfectly, from time to time however it returns #NO MATCH this is without exception for…","snippet":"Hi Everyone, Thanks to some very helpful advice on here I've amended our company quotations sheet to reference a credit checking sheet and then show if a client has been credit…","categoryID":322,"dateInserted":"2023-08-15T09:52:02+00:00","dateUpdated":null,"dateLastComment":"2023-08-15T19:05:12+00:00","insertUserID":19846,"insertUser":{"userID":19846,"name":"Ian Smith 2017","url":"https:\/\/community.smartsheet.com\/profile\/Ian%20Smith%202017","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-15T15:10:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-15T20:43:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":40,"score":null,"hot":3384221234,"url":"https:\/\/community.smartsheet.com\/discussion\/108950\/vlookup-sometimes-returns-no-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108950\/vlookup-sometimes-returns-no-match","format":"Rich","lastPost":{"discussionID":108950,"commentID":390730,"name":"Re: vLOOKUP SOMETIMES RETURNS NO MATCH","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390730#Comment_390730","dateInserted":"2023-08-15T19:05:12+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-15T20:43:49+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/SSMBWV1K9MPL\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-15T14:19:03+00:00","dateAnswered":"2023-08-15T12:04:34+00:00","acceptedAnswers":[{"commentID":390636,"body":"
Try changing the final argument of the VLOOKUPs to false. \"true\" (or 1) will only search for an approximate match whereas \"fasle\" (without the quotes) searches for an exact match.<\/p>
<\/p>
I also suggest looking in to an INDEX\/MATCH instead of a VLOOKUP in general because it allows for MUCH more flexibility and provides better performance and management overall.<\/p>
=INDEX({Only The Column You Want Pulled}, MATCH(Customer@row, {Only The Customer Column}, 0))<\/p>
<\/p>
Since you are referencing each column as individual ranges, the order of the columns in the reference table doesn't matter. They can be in any order and can be rearranged on the source sheet as needed without worrying about breaking any formulas looking at them. It also means that you don't have to reference every column in between the pull column and the match column which can mean less cells being referenced which can increase sheet performance.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
