count distinct function in combination with Countifs?

Random

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?

Jeff Reisman

The syntax is:

=COUNT(DISTINCT(range))

https://help.smartsheet.com/function/distinct

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(

Random

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!

Jeff Reisman

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