CountDistinctIf

The CountDistinctIf function returns the number of unique values within a column or group for which all given conditions are true. You can use operators in conditions.

📘

The CountDistinctIf function is an aggregate function.

Aggregate functions evaluate one or more rows of data and return a single value.

In a table element, the aggregate is calculated for each grouping. For information on how to add a grouping with an aggregate calculation to a table, see Group columns in a table.

In a table with no groupings, the aggregate is calculated for each row. For information on how to calculate summary statistics across all rows in a table, see Add summary statistics to a table.

To learn more about using aggregate functions, see Building complex formulas with grouped data.

Syntax

CountDistinctIf([field], condition 1, condition 2+...)

The function has the following arguments:

field

Required

The column of values to be counted. Duplicate and Null or empty values are skipped.

condition 1

Required

The condition to test. If the condition is true, then the row will be counted.

condition 2+

Optional

Additional conditions to test. If multiple conditions are used, they must all be True for the row to be counted.

Examples

CountDistinctIf([City], [Population] > 5000)

Finds the number of unique city names where the population is greater than 5000.

CountDistinctIf([City], [Population] 5000 AND [Population] < 10000, [State] = "Ohio")

Finds the number of unique city names where the population is between 5000 and 10,000 and the state is Ohio.