How to change formulas when a filter is applied?

Maya S

I want to create a master sheet that connects to a report for each customer in the data. In this master sheet I have a column for "Average process completion time". In the master sheet, I want it to display the average completion time for all customers, which it already does. However, when I filter a customer in their respective report, it still displays the total number for all customers, not just the one I selected. Is there a way I can get my formula to change and only display the averages for the customer I have selected?

=Chris Palmer

Hi @Maya S

If there is a specific criteria that identifies the selection of customers you can use =AVERAGEIF or perhaps =SUMIFS. We would have to see the data you're working with however if you need assistance writing a formula.

Maya S

Hi @Mr. Chris

I have a column labeled "Customer" that I would be using for my filter. I also have a column labeled "Process Work Days" which takes the number of days from the start of the process to the end. Currently for my "Average Process Completion Time" I am using this simple formula:

=IFERROR(AVG([Process Work Days]:[Process Work Days]), 0)

I am building this sheet for someone else and am hoping to make it as user friendly as possible, so I didn't want them to have to keep typing the =AVERAGEIF function for each individual customer as there are about a hundred. I thought when I filtered the entire sheet based on customer it would update this column rather than keeping the average for all customers. I considered using a dropdown box to select the customer which would update the formula but that hasn't been working out for me either. I am very new to Smartsheet, so I apologize if I am providing the wrong information. Is there a way to update the formula without typing the customer into it?

Paul Newcome

Are you able to provide some screenshots for context?