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