# count distinct function in combination with Countifs?

Options
✭✭
edited 07/06/22

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?

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!