Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Creating a Sum for Filtered Rows

Andy.Mott ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

I have a Sheet which contains a complete list of all invoices, which Customer they are for, the Invoice Total and an Invoice Paid column which would show the balance of the Invoice. I have created a number of Filters for all of my Customers, so that I can filter straight to one Customer and see their outstanding balance. This creates a statement for me. 

Is there a way of calculating the sum of the Invoices that are filtered? At present, the range it is trying to calculate is all of my rows whether filtered or not.

Any help would be great :)





  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I don't have an exact solution for you... but what if your sumif formula pointed to a blank row with a dropdown that has the data you need. You select the customer before you run your filter and tie your sumif statement to the dropdown field? 

    In my example screenshot attached, I created a header row that had my dropdown and explanatory text in the sum column, so it wouldn't be counted, and then created a sum field that would sum any customer displayed in customer 1, and sum the number column. I only see the sum of whichever customer I select. If your filter is based on the customer column you'd have to make sure to select the sum dropdown first before you run the filter.

    =SUMIF(Customer:Customer, =Customer1, Number:Number)

    Hopefully, that made sense. wink


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I would probably create a customer section (collapsible) for permanent SUMIF() for each customer. The filter would show this additional row for each customer when selected.

    Mike's version requires one to remember to put the customer in and then filter. I'd probably do this wrong a few times and then change to my version. It also requires an extra action for each filtering operation.

    Since you have already set up the filters, that implies there aren't too many customers to add a SUMIF for each.

    Both solutions require an additional column - for mine you might be able to use an existing column for the totals.


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Good point Craig! I'd probably forget and do the same thing! Doing what he suggested for each client would ensure when you filtered that the appropriate client's sum would appear in the filter. Let us know if you need a more detailed explanation. 

  • douglas.wagner65156
    edited 10/15/18


    Has Smartsheet provided a better solution to sum up filtered numbers?  It has been nearly a year since the last post.

    My situation is to track by quarters cost against a specific cost code.  I want to filter by quarter and then have the sum show the total for that  quarter.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 10/16/18

    Hi Douglas,

    There are features coming that would help.  The new features were mentioned at the Engage conference and until they arrive there could be some possible workarounds. It depends on your specific use case and set up. 


    Andrée Starå - Workflow Consultant @ Get Done Consulting 


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


    Hi Andrée,

    Do you know if there is a way to create a Sum for Filtered Rows that has been put in place since your last post ?

    Thank you !


This discussion has been closed.