Formula to only count unique cells, no duplicates
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!
Best Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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).
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Is there a limitation with the distinct formula? after about 250 cells, it gives me back an answer of 1.
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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).
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
No worries at all! Glad we could figure it out together. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!