Counting unique values and determining count of each

Jason March

Hello,

I am rather new to Smartsheet formulas and what I am trying to do is take a list of countries for multiple submission projects and view a rollup of how many submissions per country I have.

So far I have been able to roll up the number of submissions per individual using COUNTIFS and looking for "In Progress" and the individual but since I have many countries that are duplicates, I have not been able to successfully count how many submissions per country are in progress. Ultimately I would like to view this information on my dashboard.

Sean Morgan

Hello @Jason March ,

Here's an example of the Formula you may want to use: =COUNTIFS([Country of Submission]:[Country of Submission], Summary@row, Status:Status, "In Progress"). This was placed in the "Tally" Columns Cells.

Furthermore, here is how I've laid out my Sheet:

I added in extra Columns so that when referencing a range E.G ([Country of Submission]:[Country of Submission], it doesn't include data in the row above the blue separating line. This will allow the Formula to calculate more logically.

Additionally, I used Summary@row as this will look for the Country on the left hand side cells, adjacent to the Formula cells, rather than manually typing "UK" into the Formula

If I've misunderstood your issue, or if you have any additional questions, please let me know!

Regards

Sean

Sean Morgan

Hello @Jason March ,

Here's an example of the Formula you may want to use: =COUNTIFS([Country of Submission]:[Country of Submission], Summary@row, Status:Status, "In Progress"). This was placed in the "Tally" Columns Cells.

Furthermore, here is how I've laid out my Sheet:

I added in extra Columns so that when referencing a range E.G ([Country of Submission]:[Country of Submission], it doesn't include data in the row above the blue separating line. This will allow the Formula to calculate more logically.

Additionally, I used Summary@row as this will look for the Country on the left hand side cells, adjacent to the Formula cells, rather than manually typing "UK" into the Formula

If I've misunderstood your issue, or if you have any additional questions, please let me know!

Regards

Sean

Jason March

Thank you, @Sean Morgan . That worked. My next question is how would I summarize the unique values into my roll up section. For example:

I want to take this:

and summarize unique countries to look at one quick view of what countries I have in progress.