# 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
###### Who is Participating?

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

Commented:
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
Commented:
You can simply use this formula...

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

Saurabh...
sumif.xlsx
Commented:
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" .
Author 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
Commented:
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...

Experts Exchange Solution brought to you by