Static Cell References

John McLeod
John McLeod ✭✭
edited 12/09/19 inFormulas and Functions

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.

smartsheet formula image v2.PNG

Tags:

Comments

  • J. Craig Williams
    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

  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
You would use an IF\/COUNTIFS combo like so:<\/p>

=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>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"},{"commentID":387099,"body":"

Hi @Carly Chaput<\/a> <\/p>

I hope you're well and safe!<\/p>

Because you're using the Dependencies feature, you must use something like the one below.<\/p>

=[Task Name]@row + \" - \" + DATEONLY([Start Date]@row) + \" - \" + DATEONLY([End Date]@row)\n<\/pre>

Did 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":391,"urlcode":"product-development","name":"Product Development"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"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":[]}">

Trending in Formulas and Functions