We help IT Professionals succeed at work.

Crystal Sum Function based upon a Condition

pilotgl asked
Medium Priority
Last Modified: 2012-05-06
I have a crystal report sub report.  In this sub report I list items in inventory based upon their expiration date.  I have tried all ways to create a formula to calculate the following example:

Lot #            Exp. Date          Qty
123              01/28/09            10
124              03/01/09            12
125             12/28/09             10

So what my formula I want to accomplish is this, if the expiration date is greater than the current date then I want to have you add these values.  So for this example I would add lot 124 and 125 and come up with a total of 22.

I have went through about every possible formula I could think of and I haven't been able to quite accomplish the value of 22 I am trying to acheive.  I am really wanting to say if the date has not exceeded add those lines together.  If it has exceeded the current date then ignore the number in your calculation.

If anyone has an idea on how to accomplish this with a formula I would greatly appreciate it.
Thanks in advance.
Watch Question

This is fairly easy.

Create a formula as follows:

If CurrentDate > {mydatabase.expdate} then {mydatabase.qty} else 0

Then SUM on this formula.

You can suppress the formula in the details section if you don't want it to show
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Another way would be to use a running total.

Evaluate using a formula

CurrentDate < {mydatabase.expdate}

Doug's formula is almost correct.  Change the > to <

Woops!  Missed that. Thanks mlmcc!

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


I appreciate the help on this.  Thanks for the heads up on the wrong sign.  I caught that and did make the change when I created the formula.  I am getting my feet beyond wet and starting to get more heavily into Cyrstal Reports.  But there is always room to learn and am still learning that it is easier to do multiple formulas versus trying to do all the functions in one formula.  Thanks again for the help and it was greatly appreciated.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.