If you need to do a distinct count of records that meet a specific criteria you can do a Distinct Count of an IF-THEN statement. But you will run into an interesting problem. The records that don’t meet the criteria will all have an ELSE value, and that extra value also gets counted. The result is that your distinct count is usually one higher than it should be. Some users assume that if they simply skip the ELSE line of the formula, that CR will use a NULL value as the ELSE, and that the NULL won’t be counted. However, CR does not use NULL by default. If you skip the ELSE, Crystal will use the default value for that fields data type – usually an empty string (“”) or a zero. But there is away to fool CR into returning a NULL value as your ELSE value. See my (recently improved) Formula 14 for the solution.

For more tips like this you should check out my Expert Techniques series.

(Thanks to Jacques Sauvageau of Wilson Banwell PROACT for suggesting the VAL / TONUMBER. That saves a couple of steps over my original method. )