Formula that needs to reference multiple columns
Good evening,
We are looking of how we would structure a formula to calculate the % of our production job each series is responsible for.
- There are 6 series (sequences) in this job. 1 thru 6. In the "Series ID" column. There are 300 rows +/- that make up the 6 series
- The 6 series make up 100% of the job as calculated in the "% of Total Production Time" column
- what formula would I use to calculate what % of the job Series 1, 2, 3 is etc ?
Comments
-
Paul Newcome ✭✭✭✭✭✭You would use a SUMIFS() formula along the lines of...
=SUMIFS([% of Total Production Time]:[% of Total Production Time], [Series ID]:[Series ID],#)
Just put the number 1 - 6 in place of the # to get a total of all percentages for that particular series.
thinkspi.com
-
BESP10 ✭✭✭✭✭✭
Thank you , I see that this will work, my issue is going to be that I am keeping all of my metrics at the top of the sheet so my data rows do n ot start until Row 85 (parent row) and Row 86 is where the children rows start
Is there a way to start the tabulation from row 85 and include all the children rows as they get added ?
Thanks
-
BESP10 ✭✭✭✭✭✭
Good afternoon
So I am trying to make it count rows 89 to 100 but something is wrong with my formula
Anyone have any insight?
Thank you
=SUMIFS([% of Total Production Time]89):[% of Total Production Time]1000), [Series ID]:[Series ID], 1)
-
Andrée Starå ✭✭✭✭✭✭Hi,
Your formula is referencing 89-1000 so change it to 100 and should be good to go.
Did it work?
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
Yes. Simply replace the Column:Column range with a CHILDREN() range.
=SUMIFS(CHILDREN([% of Total Production Time]85), CHILDREN([Series ID]85),#)
thinkspi.com
-
Paul Newcome ✭✭✭✭✭✭
The additional closed parenthesis after each cell reference will cause an error as well.
thinkspi.com
-
BESP10 ✭✭✭✭✭✭
HMMM, So sorry guys,
It is showing me a zero, per below snap shot
I am stumped
=SUMIFS(CHILDREN([% of Total Production Time]89), CHILDREN([Series ID]89), 1)
-
Paul Newcome ✭✭✭✭✭✭
The [% of Total Production Time] column... Is that numbers in the column and then the column formatted as a percentage or is the % being added manually?
thinkspi.com
Help Article Resources
Categories
You should be able to use this formula to accomplish this:<\/p>
=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>
Example output:<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/0T1JMLQOPB6K\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nHope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T13:16:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T13:16:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":23,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T13:16:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"
Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>
=IF([Month Column]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>
Replace [Month Column] with your column name.<\/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":106913,"type":"question","name":"SUMIF with a checkbox","excerpt":"I'm trying to sum a row of amounts (Parking Revenue Regular:Private boat parking revenue) into the column \"Total Parking Revenue\", but only if the checkbox \"Payment Voucher\" isn't checked. When I add the rows using this formula without the checkbox, it works (formula on first row only): =SUM([Parking Revenue…","categoryID":322,"dateInserted":"2023-06-26T18:20:45+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162776,"lastUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3375611052,"url":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106913\/sumif-with-a-checkbox","format":"Rich","lastPost":{"discussionID":106913,"commentID":382400,"name":"Re: SUMIF with a checkbox","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382400#Comment_382400","dateInserted":"2023-06-26T19:03:27+00:00","insertUserID":162776,"insertUser":{"userID":162776,"name":"declark","title":"Regional Director","url":"https:\/\/community.smartsheet.com\/profile\/declark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4cFWuLGjTPw!x-tXMZGznvw!Dlin9vWXHb9","dateLastActive":"2023-06-27T14:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/3KE67LQDZX4M\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T19:03:34+00:00","dateAnswered":"2023-06-26T18:31:26+00:00","acceptedAnswers":[{"commentID":382384,"body":"
Try IF([payment voucher]@row=0,Sum([Parking Revenue Regular]@row:[Private boat parking revenue]@row),\"//www.santa-greenland.com/community/discussion/comment/\")<\/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":[]}">