Distinct Count of an If-Then-Else Formula:
If you want to know how many records meet a criteria, you use an If-Then-Else formula as described in formula 10 and sum the formula.However, if you need to do a distinct count of a field in these records, you run into a problem. The records that don't meet the criteria have an else value that also gets counted. The result is that your distinct count is usually one higher than it should be. Some CR users assume that if they simply skip the else line of the formula, that CR will use a NULL value as the else, and then the NULL wouldn't be counted. However, CR does not use an else of NULL by default. It will use the default value for the data type of the THEN value. That usually means and empty string value ("") or a zero for numerics. But here is a way to fool CR into returning a NULL value as your ELSE value:
1) Create a formula called "Null" and save it without any contents
2) Create an if-then-else formula:
if {criteria.field} = "X" //whatever your criteria is
then {table.ID}
//the field you are distinct counting
else {@null}
// the formula field you created above
// if your THEN field is a number use Val ({@null}) or ToNumber ({@null}) as
your ELSE
3) Do distinct counts of the if-then-else formula.
(Thanks to Jacques Sauvageau of Wilson Banwell PROACT for suggesting the VAL / TONUMBER. Saves a couple of steps over my original method. )