phone: (540)338-0194
email: ken@kenhamady.com



Conditional Total using an If-Then-Else formula:

One of the most common ways to do a conditional total is to use an If-Then-Else formula.  This has an advantage over the other techniques because it can be used to display a total at the beginning of the report or group.  The other techniques can only display their totals at the end of the repor or group.  The If-Then-Else formula technique requires 2 steps:

1) Create an If-Then-Else formula for each condition that you wish to total.  The result of the formula will be a 1 in cases where you wish to count the condition or a numeric field in cases where you wish to Sum the field.   For instance to count records in the state of Virginia:

 if {Customer.Region}  = 'VA'
 then 1
 else 0


To sum records in the state of Virginia:

 if {Customer.Region}  = 'VA'
 then {Orders.Amount}
 else 0


2) Insert a normal Summary, Subtotal or Grand Total of the formula using the Sum operation.  Even when trying to count conditional records with the first formula above, you must use the Sum operation.  Counting the formula will also count the zeros.