Report Sum - Conditional Sum

  • Thread starter Thread starter j.t.w
  • Start date Start date
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
 
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.
 
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
 
Back
Top