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
Check out the Formula Handbook template!