Creating a Sum for Filtered Rows

Andy.Mott

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

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. 

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.

Craig

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

Hi,

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å

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. 

Best,

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

ERIC VEDI

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 !

Eric