Excel Formula question

From the following tables in excel, I want table A under total invoiced for Job#2001 to calculate the sum of Sheet2 table B where column B = 'Invoice'
, 2002 would be the total sum where tableB column = 'Invoice', etc.... What kind of formula would I need in Table A, Column B?
I am thinking a sum with a lookup, but unsure the correct formula.
Thx!
Sheet$1
TableA
Job#                   TotalInvoiced
2001                          ???
2002                         ???
2003

SHeet$2
TableB                          
Job#                               InvoiceType          Invoice Amount
2001                               Invoice                   500.00
2001                           PrePayment              200.00
2001                               Invoice                   300.00
2002                               Invoice                    100.00
2002                                Credit                     50.00
2001                                 invoice                 150.00
BKennedy2008Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
You can do a sum formula with a lookup like you suggested but a more efficient way to do it is by using a pivot table.

- Highlight Table B
- Go to the Insert tab and click Pivot Table.
- You can select "Existing Sheet" and click a desired position on Sheet A
- Place "Job#" in the "ROWS" quadrant
- Place "Invoice Amount" in the Values "Quadrant"
- Place "invoice type" in the "Filters" quadrant.

Hope this helps
0
Saurabh Singh TeotiaCommented:
You can simply use this formula...

=SUMIF(Tableb[Job'#],[@[JOB'#]],Tableb[Invoice Amount])

Open in new window


workbook for your reference...

Saurabh...
sumif.xlsx
0
Tj aCommented:
Oh I forgot to mention that once you have all the steps in my previous comment, if you look right ontop of your pivot table it should say "Invoice Type" (All). You can click the dropdown arrow and select "Invoice" to only show sums for records with Invoice type, "Invoice" .
0
BKennedy2008Author Commented:
Thanks, in this situation, I needed a formula for this. I have found a solution, to use sumifs, It works perfect when wanting to sum values  based on multiple criteria items
0
Saurabh Singh TeotiaCommented:
Yeah Sumifs is a extended version of Sumif only where you can give multiple conditions to sum all the values which meet these multiple conditions..

Alternatively you can use sumproduct or array to do this as well for the same...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.