# Counting unique values and determining count of each

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.

• Employee

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

Regards

Sean

• Employee

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

Regards

Sean

• 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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!