Filter and Calculation

Options
Teresa Lai
Teresa Lai ✭✭✭
edited 09/26/22 in Formulas and Functions

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.


Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @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)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • Teresa Lai
    Teresa Lai ✭✭✭
    edited 09/26/22
    Options

    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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!