Description
The COUNTDISTINCT function counts the number of distinct non-blank values in a subform's field or in two or more fields.
Usage
COUNT(VALUE1, VALUE2, VALUE3, ...)
COUNT(SUBFORM_FIELD.FIELD_NAME)
Remarks
When used with subforms, the argument to COUNTDISTINCT() must include a reference to the form's subform field and then the field within the subform, using the dot notation.
The COUNTDISTINCT function accepts arguments of any type.
Examples
Counting number of countries among a project's sites
If you have a Projects form, with a subform containing the cities where the project is being implemented, you might want to know the distinct number of countries where the project is being implemented.
COUNTDISTINCT(CITIES.COUNTRY)
If you have a project that has the following subrecords:
| City | Country | Budget |
|---|---|---|
| Kinshasa | DRC | 10,000 |
| Goma | DRC | 5,000 |
| Gisenyi | Rwanda | 5,000 |
Then the result of COUNTDISTINCT(CITIES.COUNTRY) would be 2, though COUNT(CITIES.COUNTRY) would be 3.