Assistance with Creating a Formula for Top 10

Sese Bennett
Sese Bennett ✭✭
edited 05/28/20 in Formulas and Functions

Hello Smartsheet experts! I have a question that I am hoping you can assist with.

I am attempting to build the back-end for a Dashboard widget that displays the top 10 occurrences of a text name that is pulled from the "Name" column in three different sheets (sheet A, sheet B, and sheet C). All three sheets contain the same field names. Additionally, I would also like to be able to include only those values that meet a specific criteria called "Non-Compliant" specified in another drop down box field (single select).

So initially, I would like to know how to create a formula to pull specific text from three different sheets, count those text instances up, and then be able to sort from highest to lowest (so that I can add a chart via Dashboard reporting).

Ive done a bit of reading through the community forums but I think I am confusing myself more than helping. If someone would be kind enough to point me in the right general direction, I would be very grateful.



Best Answer


  • Ramzi K
    Ramzi K ✭✭✭✭✭

    Hi @Sese Bennett,

    Without seeing examples, I will make some assumptions and recommendations.

    1. Create a new sheet that you can use to gather all the data. Add a Name column as the first column.
    2. Enter all the names you want to count in the first column.
    3. In the second column (call it Count) use a formula that looks something like this: =COUNTIFS({Name Sheet 1 - Name Column}, Name@row, {Name Sheet 1 - Non-Compliant Column}, "Some Value")
    4. This technique uses external sheet references - more info here -
    5. After the data is all gathered up, you should have names in one column and counts of occurances with the two criteria (Name from first column and Non-Compliant value for each row) and in the other the counts from all sheets.
    6. Now you can sort the list or better yet, create a report and do your sorting there.

    I hope this helps. Happy to continue to help but probably need samples, screen shots, etc.

    Hopefully, this will get you started.



    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (

    Feel free to email me:

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Sese Bennett

    @Ramzi @cmondo Thank you both for your great responses. I am always amazed at how good some folks here are with Smartsheet! I tested both approaches and decided to go with cmondo's approach as it better suited what I needed (although both worked perfectly!).

    Thanks again for your help. It is much appreciated!

  • Shelby
    Shelby ✭✭✭✭✭
    edited 03/18/21

    @Chris Mondeau

    I have a similar need to implement the solution that you provided above, however, mine varies a little.

    I'm creating a Safety Metrics sheet to analyze the data that is in our Safety Audit Intake sheet. I'm trying to figure out the best way to provide the Most Non-Compliant safety inspection category (e.g. COVID Safety Protocols, PPE, First Aid, etc.) for each project. There are 18 inspection categories and each category has a varying number of inspection items. So the COVID Safety Protocols category has 5 inspection items while the PPE inspection category has 7 inspection items and the First Aid category has 3 inspection items...etc. See screenshots below of the Safety Audit Intake sheet Inspection Categories and Items.

    The metric I'm trying to return is the Most Non-Compliant Safety Inspection Category for each of our projects. For example:

    Project A most non-compliant inspection category is PPE

    Project B most non-compliant inspection category is First Aid

    Project C...etc.

    My question is, what's the best formula to return the most non-compliant category when there are varying inspection items per category? Your help is much appreciated

    @Paul Newcome feel free to chime in if you have a solution as well

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!