Need formula to find and sum values in a different spreadsheet
 

I have a speadsheet (Jobs) in which job# is in Column A
and job cost is in Column B, as in:

Col. A Col. B
0601-1 $25.00
0602-1 $0.00

I have a second spreadsheet (Expenses) in which job# is in
Column A, "delivery" is one of the possible items in Column B,
and the cost of the delivery is in Column C, as in:

Col. A Col. B Col. C
0601-1 Delivery $30.00
0602-1 Paper $100.00
0601-1 Delivery $40.00

Note that a job# can have more than one delivery attached to
it.

What I need is a formula that I can put in each cell Column B of
the Jobs spreadsheet with a job# in Column A that will do the
following:
1. Go to the Expenses speadsheet.
2. Find each job# in its Column A that has "Delivery" in the
adjacent cell in Column B.
3. Sum all the delivery costs for that job that are in Column C.
4. Add that sum back into the appropriate cell in the Jobs
spreadsheet, which may already have a $ value.

Thanks in advance for your help,
Howard

Try:

=sumproduct(--(Expenses!A$1:A$500=A1),--(Expenses!B$1:B$500="Delivery"),
Expenses!C$1:C$500)

"Watercolor artist" wrote:

I have a speadsheet (Jobs) in which job# is in Column A
and job cost is in Column B, as in:

Col. A Col. B
0601-1 $25.00
0602-1 $0.00

I have a second spreadsheet (Expenses) in which job# is in
Column A, "delivery" is one of the possible items in Column B,
and the cost of the delivery is in Column C, as in:

Col. A Col. B Col. C
0601-1 Delivery $30.00
0602-1 Paper $100.00
0601-1 Delivery $40.00

Note that a job# can have more than one delivery attached to
it.

What I need is a formula that I can put in each cell Column B of
the Jobs spreadsheet with a job# in Column A that will do the
following:
1. Go to the Expenses speadsheet.
2. Find each job# in its Column A that has "Delivery" in the
adjacent cell in Column B.
3. Sum all the delivery costs for that job that are in Column C.
4. Add that sum back into the appropriate cell in the Jobs
spreadsheet, which may already have a $ value.

Thanks in advance for your help,
Howard

From where does the existing value in Sh1 column B come?
I will assume an existing equation (ExEq)

in B1 enter
=ExEq+sumproduct(--(Sheet2!$A$1:$A$1000=Sheet1!A1),--Sheet2!$B$1:$B$1000="Delivery"),Sheet2!$C$1:$C$100 0)


"Watercolor artist" wrote:

I have a speadsheet (Jobs) in which job# is in Column A
and job cost is in Column B, as in:

Col. A Col. B
0601-1 $25.00
0602-1 $0.00

I have a second spreadsheet (Expenses) in which job# is in
Column A, "delivery" is one of the possible items in Column B,
and the cost of the delivery is in Column C, as in:

Col. A Col. B Col. C
0601-1 Delivery $30.00
0602-1 Paper $100.00
0601-1 Delivery $40.00

Note that a job# can have more than one delivery attached to
it.

What I need is a formula that I can put in each cell Column B of
the Jobs spreadsheet with a job# in Column A that will do the
following:
1. Go to the Expenses speadsheet.
2. Find each job# in its Column A that has "Delivery" in the
adjacent cell in Column B.
3. Sum all the delivery costs for that job that are in Column C.
4. Add that sum back into the appropriate cell in the Jobs
spreadsheet, which may already have a $ value.

Thanks in advance for your help,
Howard

I enter it manually. There's no formula.

Howard

"bj" wrote:

From where does the existing value in Sh1 column B come?
I will assume an existing equation (ExEq)

in B1 enter
=ExEq+sumproduct(--(Sheet2!$A$1:$A$1000=Sheet1!A1),--Sheet2!$B$1:$B$1000="Delivery"),Sheet2!$C$1:$C$100 0)


"Watercolor artist" wrote:

I have a speadsheet (Jobs) in which job# is in Column A
and job cost is in Column B, as in:

Col. A Col. B
0601-1 $25.00
0602-1 $0.00

I have a second spreadsheet (Expenses) in which job# is in
Column A, "delivery" is one of the possible items in Column B,
and the cost of the delivery is in Column C, as in:

Col. A Col. B Col. C
0601-1 Delivery $30.00
0602-1 Paper $100.00
0601-1 Delivery $40.00

Note that a job# can have more than one delivery attached to
it.

What I need is a formula that I can put in each cell Column B of
the Jobs spreadsheet with a job# in Column A that will do the
following:
1. Go to the Expenses speadsheet.
2. Find each job# in its Column A that has "Delivery" in the
adjacent cell in Column B.
3. Sum all the delivery costs for that job that are in Column C.
4. Add that sum back into the appropriate cell in the Jobs
spreadsheet, which may already have a $ value.

Thanks in advance for your help,
Howard