Filter and Calculation

Teresa Lai

Can we keep specific rows visible when applying filters?

• Can parent rows visible when using filter?
• Can we apply multiple filters? I've seen the articles discussing about it but not sure if it works now.
• I'd like to keep the rows for calculation (Functional Summary to Column15) visible and calculate the numbers when I use a filter to view specific contents. Is it possible? Thank you.

Jeff Reisman

@Teresa Lai

In Smartsheet, filters only affect what rows you can see on the screen. The underlying formulas do not change to only include filtered rows.

I would suggest creating a metrics sheet where you use formulas to calculate totals from your main sheet based on criteria, or using Summary fields inside your main sheet to do the same thing.

Another option is adding some dropdown fields that let you choose different variables that your counting formulas use. For instance, if you want to count rows in Complete status where the category is System Operation, you could create columns for Metrics Status and Metrics Category, then create a COUNTIFS that references those fields. When you choose different options in those fields, your count formula would count different things:

=COUNTIFS(Status:Status, [Metrics Status]@row, Category:Category, [Metrics Category]@row)

Andrée Starå

Hi @Teresa Lai

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

Another option could be a Report with Grouping/Summing.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Teresa Lai

Hi Andree, Jeff,

Thank you for the feedback. I'd like to provide more background on how my sheet/dynamic view/report synchronize with one another so you have better understanding on my questions.

Firstly, the summary of how many actions are completed and not completed by System Manager (or other roles under "Person Accountable") are calculated in Sheet. I will need a summary for CA, a summary for MRR and there are more phases so I do not consider utilize Sheet Summary at the moment.

Secondly, a Dynamic View is crated based on the selected columns in Sheet and I do not want the users to see the rows of "Planning - CA(offline)" and "Planning- MRR" (where summary of actions is calculated in Sheet). But it seems they will show up as I have the "Done" Column and "Incomplete" Column with Checkbox property.

Third, I want a dashboard to display visualized project status so I created a Report and the data is from Sheet.

Can you advise how I can modify in order not to show the Planning-CA (Offline) and Planning - MRR rows in Dynamic View?