Report Sum - Conditional Sum

J

j.t.w

Hi All,

I found this in a posting by Duane Hookom and I am trying to modify it
to fit my needs. He writes...

You can add a text box to a group or report footer that uses syntax
like:
=Abs(Sum( (Job=1 or Job=2)*Sales) )
This will sum Sales for only Job 1 or 2.


This is what I have...

=Sum(([RegCategory]="Misc" Or
[RegCategory]="Over/Short")*[RegCategoryAmount])

I am trying to add the RegCategoryAmount field where RegCategory is
"Misc" or "Over/Short". The problem I'm having is that typically, the
sum should be a negative number. I took out the Abs portion hoping
that would fix it, but it didn't. What can I do to correct this?

I noticed that there is an "*" in the syntax. Could someone explain
how this expression works?

Thanks for your help.
j.t.w
 
D

Duane Hookom

Try:
=Sum(Abs([RegCategory]="Misc" Or
[RegCategory]="Over/Short")*[RegCategoryAmount])
The expression inside the Abs() will evaluate to either 0/false or -1/True.
Abs() converts the -1 to 1 and "*" multiplies the 1 or 0 times
RegCategoryAmount. Summing the result should be the value you are after.
 
J

j.t.w

Duane, Thank you...It works perfectly. Also, thanks for the
explanation.

You guys (and gals) are great! This NG is such a wonderful resource.
What would I do without everyone's help?!

Have a great day!
j.t.w
 
Top