count distinct function in combination with Countifs?
when I have 3 countifs (=countifs(range, condition, etc), then I need to add count(distinct(range), how to write this formula? tried several ways, but none of them works, can anyone help me?
Answers
-
The syntax is:
=COUNT(DISTINCT(range))
The range can be a column or row or part of either on the same sheet:
All of ColumnA: =COUNT(DISTINCT(ColumnA:ColumnA))
Part of Column A (rows 13 to 34): =COUNT(DISTINCT([ColumnA]13:[ColumnA]34))
All of the given row: =COUNT(DISTINCT(ColumnA@row:ColumnZ@row))
Part of the given row: =COUNT(DISTINCT(ColumnF@row:ColumnT@row))
A particular row (row 7): =COUNT(DISTINCT([ColumnA]7:[ColumnZ]7))
or the range can reference a column or row range in another sheet (using the "Reference Another Sheet" link that appears when you start typing your formula into a cell and get to here: =COUNT(DISTINCT(
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you, but this is only getting distinct count from one column, I need to find the datas in 3 different columns that meets my criteria, and then out of these data, count the distinct number of 4th column.
To give an example:
=countifs(Column A,"xxx",column b,"111",column c,"abc"), 2 will be the result of these countifs, but then distinct count the column D with the data that matches the countifs, which answer would be 1.
so how to write this combine fomula of countifs+count distinct?
Thank you!
-
@Random You could JOIN your three column values into a helper column, and then count the distinct of that helper column. =JOIN([Column A]@row:[Column C]@row)
In your example, your helper column would contain:
xxx111abc
yyy222xyz
111333abc
xxx111abc
Then =COUNT(DISTINCT(HelperColumn:HelperColumn)) will give you the number of distinct combinations. With the four rows above, the distinct count would be 3.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!