Sum with Criteria

[cydud3]

I currently have a report with a field "Amount". At the report footer, I'd like to have a sum of the amount but only those that are above a particular number. For example, I only want to sum those above 10,000. So, my report should look like this:

AMOUNT
12,000
300
14,000
4,000
9,500

Total: 26,000

Is this possible? I know I can use =Sum(Amount) but it sums all the numbers. By the way, the individual amounts that needs to be summed are already Sum Aggregates. Hopefully my explanation makes sense.

Thanks.

2B||!2B

 

[rjoubert]

Create another text field in the detail of your report. You can set the Visible property to False so it doesn't display. The Control source for this field would be:

IIF([Amount] >= 10000, [Amount], 0)

Then, in your report footer, add your Sum field, but sum the new field rather than the Amount field.

 

[cydud3]

Thanks. Now why didn't I think of that?

I'll try it out right now.

2B||!2B