# Help needed Calculating amounts

#### zookeeperbobbie

##### Board Regular
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

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

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.

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

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.

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

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.

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....

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?

Replies
3
Views
361
Replies
1
Views
391
Replies
28
Views
2K
Replies
1
Views
713
Replies
5
Views
625

1,219,812
Messages
6,150,367
Members
450,955
Latest member
rose8693

### 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.

### Which adblocker are you using?

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

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