Hi everyone,

I'm new to writing formulas in Crystal.  I have version XI.

What I'm trying to do is count the number of records in a field that fall within a range, then return that result. 

A simplified example of what I want to end up with:

Grouped data (Orders by Company name)

0-100 pounds       5
101-200 pounds    6
201-300 pounds    1

I've experimented with the Count formula and control structures, but I don't see the solution.  I noticed there are Range functions but don't understand how to make them work here.  Can anyone please help me put it together?


Edited by zann - 29 Sep 2010 at 8:52am

Just found one solution, however messy it is.   In case someone needs it, first create formula fields with this:

if ({table.field} = "criteria")
then 1
else 0

You can then create a second formula to sum this field to get a count of records matching the criteria and place this field on the report.  This is working for me so far.

Can you filter the record set using the Select Expert so your data only contains the fields that fall in the range you want?

If your report needs to select a wider range of data and you only want to count a subset, could you group the data and count the data in that group?

Have you looked at DistinctCount vs Count?

You could create a formula like this:

if {table.field} >= lowerlimit and {table.field} <= upperlimit then
   1
else
   0

You can then sum the formula to count the records you want.