Assistance with Creating a Formula for Top 10

Sese Bennett

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.

Thanks!

Sese

Chris Mondeau

Hi Sese,

Hopefully this might help:

I made a summary sheet called "rank" which counts all rows on sheets that match the "Name" and Non-Compliant" status from a few sheets (A, B, C). The last column on the rank sheet just uses the rank function from the highest count of the total number of non-compliant names. You could create a report that sorts these so it's dynamic from your dashboard.

Here's what Sheet A, B, C look like. The cross sheet range names {A_Name} just reference the entire column "Name" for sheet A. {A_Status} is the entire column for status.

In the rank sheet, I used the countifs function to count all instances of the name in the row. In this picture it's counting all rows on the A sheet where the value is equal to cat and the status is non-compliant.

In this pic I'm summing sheets A, B, C together for reach row.

The last column rank assigns a value based on the number of non-compliant task for the row from the entire column.

Hopefully this helps!

Non-Compliant Formula: =COUNTIFS({A_Name}, @cell = Name@row, {A_Status}, "Non-Compliant")

Non-Compliant Total: =SUM([Non-Compliant (A)]@row:[Non-Compliant (C)]@row)

Rank: =RANKEQ([Non-Compliant (Total)]@row, [Non-Compliant (Total)]:[Non-Compliant (Total)])

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 - https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
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.

Cheers,

Ramzi

Chris Mondeau

Hi Sese,

Hopefully this might help:

I made a summary sheet called "rank" which counts all rows on sheets that match the "Name" and Non-Compliant" status from a few sheets (A, B, C). The last column on the rank sheet just uses the rank function from the highest count of the total number of non-compliant names. You could create a report that sorts these so it's dynamic from your dashboard.

Here's what Sheet A, B, C look like. The cross sheet range names {A_Name} just reference the entire column "Name" for sheet A. {A_Status} is the entire column for status.

In the rank sheet, I used the countifs function to count all instances of the name in the row. In this picture it's counting all rows on the A sheet where the value is equal to cat and the status is non-compliant.

In this pic I'm summing sheets A, B, C together for reach row.

The last column rank assigns a value based on the number of non-compliant task for the row from the entire column.

Hopefully this helps!

Non-Compliant Formula: =COUNTIFS({A_Name}, @cell = Name@row, {A_Status}, "Non-Compliant")

Non-Compliant Total: =SUM([Non-Compliant (A)]@row:[Non-Compliant (C)]@row)

Rank: =RANKEQ([Non-Compliant (Total)]@row, [Non-Compliant (Total)]:[Non-Compliant (Total)])

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

@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