Help needed Calculating amounts

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Hi- I have a table with three rows of sales- I need to create a new column that will give me the total of all three. I have tried doing formulas and a query but Its just not working--- I;m getting all kinds of errors... Any help would be greatly appreciated.

(eventually i am going to insert either the table or a query into a form to make it a subform)
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,322
Office Version
  1. 365
Platform
  1. Windows
Sounds like you might have some design problems.

What is the layout of your tables?

I am a little confused when you say you have different rows of data, but want to put a grand total in a column. Do you want the total to appear with each record?

Maybe if you post an example of what you are after and your desired end result, it will make more sense.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
Well Right now i have my information set up in a table-
each record has three columns of amounts. (Sorry about calling them rows before) so each record(line) has a vendor name, po # and 1, 2, or 3 different sales amounts.

What I;m trying to achieve is to have these columns for each record totaled.

Im not sure how to post a picture here or i woudl do a print screen of what it looks like now...

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,322
Office Version
  1. 365
Platform
  1. Windows
That's clearer, rows and columns certainly make a big difference.

If all three amounts are on the same row (record), simply create a query with a calculated field to add them up. It will look something like:

MyTotal: [Field1] + [Field2] + [Field3]


If you need help writing expressions (calculations) in a query, check out "Create an Expression" in Access help.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117

ADVERTISEMENT

Ok read help But i have to be doing somthing wrong-
I'm comming up with a enter parimeters window.

I am using this formula in the "Built"
=Nz([Deduction 1 Amount])+Nz([Deduction 2 amount])+Nz([Deduction 3 Amount])

My query looks like this:
Field:Expr1: [Problems]
Table:Problems
Total:Sum
Sort:Accending
Show:Yes
Critera:=Nz([Deduction 1 Amount])+Nz([Deduction 2 amount])+Nz([Deduction 3 Amount])
Or:


Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,322
Office Version
  1. 365
Platform
  1. Windows
It looks like you may be getting tripped up by the lingo again.

What you posted does NOT look like anything that would be in a query. It looks like a calculated field on a Report or a Form.

If you do the calculation in a Query, you can then use the Query as the Control Source for your Report/Form, and then you won't need to try to do the calculation there in a text box.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117

ADVERTISEMENT

?? I;m confused I am doing this in a query under the design view...
I really wish I would post my file...
Thanks anyway....
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,322
Office Version
  1. 365
Platform
  1. Windows
OK, I think I see what is going on now. You had me a little confused because the calculation is in the wrong place (you put it under criteria -- you want to build it under the Field, not the Criteria).

It should look like this:
Field:Expr1: Nz([Deduction 1 Amount])+Nz([Deduction 2 amount])+Nz([Deduction 3 Amount])
Table:Problems
Sort:Accending
Show:Yes
Critera:

I don't think you want the Totals in there, so hit the Sigma icon to remove them.
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
This thing is going to be the end of me... its so agravating- Now it says there is a extra ( in the formula... maybe i have bene starting at this for too long... I dont see any extra one....
 

zookeeperbobbie

Board Regular
Joined
Feb 9, 2005
Messages
117
I fiqured it out-- Thank Goodness- Thank you so much for your help--- Could I bother you with one more problem?
Do you know how I would create a count of how many times a vendor had a record?
 

Forum statistics

Threads
1,136,706
Messages
5,677,309
Members
419,685
Latest member
hennLow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top