# Formula to only count unique cells, no duplicates

Options
✭✭✭

Hello again,

is there an =countifs formula that can be used to only count unique cells which would avoid counting duplicate cells in that column?

below i want to count W01-W06 only once for the answer to be 6 instead of counting all the duplicate W01 to =8

thanks!

Options

Hi @SRenner

Are you looking for any other criteria, or just the number of unique values? To count the Unique Values in the Store Number column, you could use a COUNT(DISTINCT formula.

Try this:

=COUNT(DISTINCT([Store Number]:[Store Number]))

If you were looking to add in more criteria (such as including a specific Date from that Date column), you could throw in the COLLECT function... for example:

=COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], Date:Date, DATE(2020, 04, 12)))

Here are some Help Center articles I used: COUNT function / DISTINCT function / COLLECT function / DATE function / Referencing Columns

Cheers,

Genevieve

Options

Hi @SRenner for your second question, could you copy/paste the formula you're using? Or provide a screen capture? I just tested up to 500 rows, with 260 unique values and it worked as expected.

For looking for specific letters, my preference to build this would be to use the COLLECT and CONTAINS function, similar to how I referenced the date above.

Try this:

=COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], [Store Number]:[Store Number], CONTAINS("R", @cell))))

This would only work if your cells that begin with R are the only values that contain an R, as it doesn't look for where the R is located (does that make sense?)

Glad you like the Community!! I love it, too. 🙂

Options

Hi @SRenner

Are you looking for any other criteria, or just the number of unique values? To count the Unique Values in the Store Number column, you could use a COUNT(DISTINCT formula.

Try this:

=COUNT(DISTINCT([Store Number]:[Store Number]))

If you were looking to add in more criteria (such as including a specific Date from that Date column), you could throw in the COLLECT function... for example:

=COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], Date:Date, DATE(2020, 04, 12)))

Here are some Help Center articles I used: COUNT function / DISTINCT function / COLLECT function / DATE function / Referencing Columns

Cheers,

Genevieve

• ✭✭✭
Options

Thanks @Genevieve P!

This worked!!

I was also wanting to only count stores that started with R and in a separate formula, only stores that started with W

I tried =countifs(DISTINCT(STORE NUMBER:STORE NUMBER), OR(LEFT(@cell) = "R")) but it came back as invalid data type. any help with this would be appreciated as well. I love this community!

thank you

Scott

• ✭✭✭
Options

Is there a limitation with the distinct formula? after about 250 cells, it gives me back an answer of 1.

Options

Hi @SRenner for your second question, could you copy/paste the formula you're using? Or provide a screen capture? I just tested up to 500 rows, with 260 unique values and it worked as expected.

For looking for specific letters, my preference to build this would be to use the COLLECT and CONTAINS function, similar to how I referenced the date above.

Try this:

=COUNT(DISTINCT(COLLECT([Store Number]:[Store Number], [Store Number]:[Store Number], CONTAINS("R", @cell))))

This would only work if your cells that begin with R are the only values that contain an R, as it doesn't look for where the R is located (does that make sense?)

Glad you like the Community!! I love it, too. 🙂

• ✭✭✭
Options
• ✭✭✭
Options

Hello @Genevieve P ,

the count(distinct(collect formula did the trick.

Also, I did have a numbers and strings in there. i took out the numbers and it worked. good thinking.

I now have a fully automated report with up the minute results.

thank you so much for the help!

Scott

Options

No worries at all! Glad we could figure it out together. 🙂

• Options

How do I use this formula referencing another sheet to count a unique field.

I have one sheet for ,my metrics and another sheet with data.

I need to count unique peoples names.

How would I use this formula?

Options

Depending on how your source sheet is set up, you could simply use a COUNT(DISTINCT, like so:

=COUNT(DISTINCT({Column with Names}))

Cheers,

Genevieve

• Options

Thanks, I also need to figure out formulas for the below.

MERTICS SHEET

Mean Review Time (b. days)

Median Review Time (b. days)

Min Review Time (b. days)

Max Review Time (b. days)

Latest Review (field would show this in "Date review returned to author"

SOURCE DATE SHEET

Columns:

Date the paper was received (In)

Date review returned to author (Out)

Options

What I would do in this instance is add a third column in your "Source Date Sheet", which simply subtracts the Date Received from the Date Returned to give you the Duration in days:

or:

=[Date review returned to author (Out)]@row - [Date the paper was received (In)]@row

Then you'll have a column with numbers that you can use for your Metrics sheet:

Cheers,

Genevieve

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!