Formula to only count unique cells, no duplicates

SRenner · 04/14/20

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!

Genevieve P. · 04/15/20

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

Genevieve P. · 04/15/20

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

Genevieve P. · 04/15/20

Hi @SRenner

Ok so I tested this and I found the same result if my column contained both string values (such as RX06323) and numerical values (such as just 6323). I believe the content of the column needs to be either all string values (with letters and numbers) or all numerical.

Do you have any values that are just numbers? If so, could you use the find and replace function to either add a letter to the Store Number, or a leading 0 (which will turn it into a string).

Genevieve P. · 04/15/20

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

SRenner · 04/15/20

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

SRenner · 04/15/20

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

Genevieve P. · 04/15/20

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

SRenner · 04/15/20

Genevieve P. · 04/15/20

Hi @SRenner

Ok so I tested this and I found the same result if my column contained both string values (such as RX06323) and numerical values (such as just 6323). I believe the content of the column needs to be either all string values (with letters and numbers) or all numerical.

Do you have any values that are just numbers? If so, could you use the find and replace function to either add a letter to the Store Number, or a leading 0 (which will turn it into a string).

SRenner · 04/15/20

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

Genevieve P. · 04/15/20

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

Rebecca N · 12/15/22

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?

Genevieve P. · 12/15/22

Hi @Rebecca N

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

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

See: COUNT Function / DISTINCT Function / Create cross sheet references to work with data in another sheet

Cheers,

Genevieve

Rebecca N · 12/15/22

https://community.smartsheet.com/discussion/comment/354513#Comment_354513

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)

Genevieve P. · 12/19/22

Hi @Rebecca N

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:

=[Date Returned]@row - [Date Received]@row

or:

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

See: Use Formulas to Perform Calculations With Dates

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

AVG Function / MEDIAN Function / MIN Function / MAX Function

Cheers,

Genevieve

Formula to only count unique cells, no duplicates

Best Answers

Answers

Help Article Resources

Categories