Static Cell References
I have a list of expenses paid to vendors. The same vendor may appear in the list multiple times. I am trying to figure out a way to summarize all payments to vendors. I had looked for the ability to do a report and show the "top 10" vendors, or a pie chart, but could not get those options to work. So, I wrote a formula that used "sumif" and summed the total spending for each vendor. I have attached a screen shot of the sheet with the formula displayed. The formula works, but when the sheet is sorted either in ascending or descending order, the ranges change, even though I used the "$" to indicate I wanted the range to remain static. Any thoughts on how to accomplish this? My ultimate goal is to be able to summarize all spending by vendor.
Thanks.
Comments
-
J. Craig Williams ✭✭✭✭✭✭
Option 1: Use a hierarchy (the blue filled row becomes the parent) and use =SUMIFS(CHILDREN(), your criteria)
Sorting won't mess with the parent-child relationships.
Option 2: Move the formula somewhere else (either on the same sheet or another sheet using X-Sheet References) and then sum the whole column.
I move most of my KPI's to other metrics sheets to avoid this and other problems.
Craig
-
John McLeod ✭✭
Thanks Craig, here are my thoughts:
- Option 1 - Accounts payable personnel will be the individuals entering the AP spending activity, so creating parent/child rows for all the spending may not be something they are comfortable with or capable of, but I do think that would be a viable solution.
- Option 2 - If I move the formula somewhere else, either on the same sheet or another sheet, won't I have to somehow enter the vendor name in that other location as well so that the formula has a reference point? In other words, If vendor ABC Company appears in the list 6 times, won't I have to enter it once in the "other location" so that the formula has a point of reference for what to sum based off of? It seems like I would have to then manage a manual list of vendors in order to get the spending totals.
If I am off on any of these then let me know. I just started using smartsheet just recently, so I am just learning what it can and can't do. In Excel I would just use a simple pivot table to let it filter out duplicates and summarize the spending. Can I do something like that in smartsheet reports?
thanks for your help.
Help Article Resources
Categories
=IF(COUNTIFS(EID:EID, @cell = EID@row, [ID Color]:[ID Color], @cell = [ID Color]@row)> 1, 1)<\/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":108077,"type":"question","name":"Auto check all check boxes in a column","excerpt":"Hello! I have a sheet with a check box column where end users select the check box to nominate a speaker which will trigger a separate workflow. The issue with individually selecting is that there can be upwards of 100 speakers on the list. Is there a way to have an option for the end user to click one check box or choose…","snippet":"Hello! I have a sheet with a check box column where end users select the check box to nominate a speaker which will trigger a separate workflow. The issue with individually…","categoryID":322,"dateInserted":"2023-07-26T00:17:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-26T16:08:28+00:00","insertUserID":156004,"insertUser":{"userID":156004,"name":"l.gann","url":"https:\/\/community.smartsheet.com\/profile\/l.gann","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wvvUtz9SPz4!BK9WwSNq73Q!1tGP-GaGrL2","dateLastActive":"2023-07-26T20:56:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-27T00:56:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":48,"score":null,"hot":3380720770,"url":"https:\/\/community.smartsheet.com\/discussion\/108077\/auto-check-all-check-boxes-in-a-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108077\/auto-check-all-check-boxes-in-a-column","format":"Rich","tagIDs":[219,254,334,448],"lastPost":{"discussionID":108077,"commentID":387208,"name":"Re: Auto check all check boxes in a column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387208#Comment_387208","dateInserted":"2023-07-26T16:08:28+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-27T00:56:40+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\/XCHWACZPRWV4\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-26T12:57:29+00:00","dateAnswered":"2023-07-26T09:04:52+00:00","acceptedAnswers":[{"commentID":387100,"body":"
Hi @l.gann<\/a> <\/p> I hope you're well and safe!<\/p> You could add a so-called helper column and reference a specific cell for check all and have a workflow trigger from it, and that would check all the boxes.<\/p> Make sense?<\/p> Would that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":108075,"type":"question","name":"Join Formula with Dates - How to eliminate the time stamp?","excerpt":"I am using the join formula to combine the task name, start date, and end date into a single column that will be used for reporting purposes. The start and end dates are formatted to only show the date, not a time, however when I use the join formula, it's giving me 'Task Name -- ##\/##\/## 8:00 AM - ##\/##\/## 4:59 PM\" and I…","snippet":"I am using the join formula to combine the task name, start date, and end date into a single column that will be used for reporting purposes. The start and end dates are formatted…","categoryID":322,"dateInserted":"2023-07-25T23:25:07+00:00","dateUpdated":null,"dateLastComment":"2023-07-26T16:37:18+00:00","insertUserID":151681,"insertUser":{"userID":151681,"name":"Carly Chaput","url":"https:\/\/community.smartsheet.com\/profile\/Carly%20Chaput","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7LDR-2jLTOc!ncHRPxz3FX0!r2XVfx-Tpm0","dateLastActive":"2023-07-26T16:36:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":151681,"lastUser":{"userID":151681,"name":"Carly Chaput","url":"https:\/\/community.smartsheet.com\/profile\/Carly%20Chaput","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7LDR-2jLTOc!ncHRPxz3FX0!r2XVfx-Tpm0","dateLastActive":"2023-07-26T16:36:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":39,"score":null,"hot":3380718745,"url":"https:\/\/community.smartsheet.com\/discussion\/108075\/join-formula-with-dates-how-to-eliminate-the-time-stamp","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108075\/join-formula-with-dates-how-to-eliminate-the-time-stamp","format":"Rich","tagIDs":[219,254,391,437,440],"lastPost":{"discussionID":108075,"commentID":387232,"name":"Re: Join Formula with Dates - How to eliminate the time stamp?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387232#Comment_387232","dateInserted":"2023-07-26T16:37:18+00:00","insertUserID":151681,"insertUser":{"userID":151681,"name":"Carly Chaput","url":"https:\/\/community.smartsheet.com\/profile\/Carly%20Chaput","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7LDR-2jLTOc!ncHRPxz3FX0!r2XVfx-Tpm0","dateLastActive":"2023-07-26T16:36:52+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\/JUP9P0NLORVI\/example-join-formula-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"example join formula.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-26T16:37:00+00:00","dateAnswered":"2023-07-26T00:23:18+00:00","acceptedAnswers":[{"commentID":387066,"body":" Hi Carly, <\/p> You could use the following formula to join without populating a timestamp: <\/p> =[Task Name]@row + \" - \" + [Start Date]@row + \" - \" + [End Date]@row<\/p>