Static Cell References

John McLeod
John McLeod ✭✭
edited 12/09/19 in Formulas 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.


smartsheet formula image v2.PNG



  • 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.


  • 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!