Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|