Subtotal specific rows in subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know how to do a subtotal for the form by using =nz(Sum([Amount])) in the
form footer. But is there a way to only sum the Amount when the value of
another field is 1,3,5, etc. but not 2, 4? Kind of like the SumIf function
is Excel.

What I have is a subform where the user can enter activities and a dollar
amount.

Amount ActivityType
--------- -------------
$50 Sales
$25 Refund
$5 Other credits

If I use Sum(Amount), the subtotal would be incorrect because Refund and
Credits need to be deducted and not added. I was thinking of adding all the
Refund and Credits and subtracting that total from sum(Amount) to get the
correct subtotal. Is this the best/correct approach? Does anyone have a
suggestion?

thank you!
 
There's a rather messy option:
=Sum(IIf([AmountType]=1 Or [AmountType]=3 Or [AmountType]=5, [Amount], 0))

A better solution might be to create a table to handle the lookups, and
include a field to indicate whether this is a plus or minus for the sake of
calculations. Fields:
AmountType Number
Sign Integer, required, validation rule: 1 or -1

You can then base the form on a query that includes the lookup table, and:
=Sum([Sign] * [Amount])
 
Allen,
I used your second suggestion and it works. thank you.

Allen Browne said:
There's a rather messy option:
=Sum(IIf([AmountType]=1 Or [AmountType]=3 Or [AmountType]=5, [Amount], 0))

A better solution might be to create a table to handle the lookups, and
include a field to indicate whether this is a plus or minus for the sake of
calculations. Fields:
AmountType Number
Sign Integer, required, validation rule: 1 or -1

You can then base the form on a query that includes the lookup table, and:
=Sum([Sign] * [Amount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

kdw said:
I know how to do a subtotal for the form by using =nz(Sum([Amount])) in the
form footer. But is there a way to only sum the Amount when the value of
another field is 1,3,5, etc. but not 2, 4? Kind of like the SumIf
function
is Excel.

What I have is a subform where the user can enter activities and a dollar
amount.

Amount ActivityType
--------- -------------
$50 Sales
$25 Refund
$5 Other credits

If I use Sum(Amount), the subtotal would be incorrect because Refund and
Credits need to be deducted and not added. I was thinking of adding all
the
Refund and Credits and subtracting that total from sum(Amount) to get the
correct subtotal. Is this the best/correct approach? Does anyone have a
suggestion?

thank you!
Click to expand...
Click to expand...
 
Back
Top