Multiple sorts/countifs of different columns

Options

Hello - new user here!

I have a new sheet with ~200 rows with ~25 columns. I have created a form to feed data into the sheet and that is working great. A few of those columns include creator name, department, category, and a few other 'metadata-type' fields. I'm struggling to create summary reports of those 'metadata-type fields' as I know there will be future values/names that are not entered yet, but I know that I will want to be able to summarize on them.

Examples of summary reports would be number of entries by category, for a given creator or department:

Name Cat1 Cat2 Cat3 TTL

Alex 15 4 8 27

Dept1 70 55 45 170

I understand how to countif and match and contains, but can I automatically create a summary that grows for each discrete value in either name or department (I'm AOK is they are different reports)?? I would expect this report to grow to 10000s of rows and cannot be filtering and recreating reports all the time. The goal would be to have all this update on our executive dashboard.

Thanks - Ben

Best Answer

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    In my opinion, the best practice is to use an external sheet to generate global metrics for your source sheet.

    This is oftentimes sufficient enough to get said metrics/KPIs onto a dashboard in the form of charts and metric widgets.

    You can also build reports off of the metrics sheet to generate reports if you want to see that data specifically.

    So this could look something like this (note I added asterisks in front of formulas so the text is visible)


    If you have multiple names and want to see the number of entries for a specific name, something like this would work

    If you repeat this structure for each category, you can classify the categories to easily generate reports using an additional column that displays the category name. In the report builder, to get a report for Name, you would reference this sheet and in the WHAT have it include rows where Report is equal to name. If you configure the Report column as a dropdown it will make it easier to do this.


    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

  • Ben Scholz
    Ben Scholz ✭✭✭
    Options

    Thank you Dan P.

    This makes sense if I knew the number of "names" - currently 25, but will grow. I need a dynamic list of those names first, then your formulas could be used to count the entries by category by name.

    Thoughts?

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

    Hi @Ben Scholz

    To add to Dan’s excellent advice.

    I agree that a so-called Master Metric sheet would work best.

    It can be structured to be dynamic, so it can grow with what you need.

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.

  • Ben Scholz
    Ben Scholz ✭✭✭
    Options

    Thanks again.........

    Building the dynamic list is the current hurdle I can get across. Yes, in a separate sheet, I'm calculating my metrics in a table, using =CountIFS to intersect the row & column to count the number of instances...........

    =COUNTIFS({Risk.Register2 Inherent Risk}, CONTAINS([Column2]$2, @cell), {Risk.Register2 RiskOwner}, $[Primary Column]3)

    How do I build the dynamic list so that as new names are added to the original sheet (or DB) my separate metrics sheet automatically adds the new name and applies the formulas?

    Ben

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You would need to use a formula that pulls one instance of each name into a delimited string. Then you can parse this list down a column or across a row.

    HERE is a link to a sheet that provides a parsing solution from another sheet down a column. It requires two "helper" columns and you would need to "pre-fill" the formula into as many rows as you anticipate needing.

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

    @Ben Scholz

    Happy to help!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best, 

    Andrée 

    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.