Results 1 to 4 of 4
Thread: Sumproduct (?)

20091109, 10:23 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Workbook 1 is a monthly summary of contracts activity. In column A is a listing of each contract number (approx 75100 each month) that had activity during the month. In column F, is the contract name, and in column H the quantity of widgets purchased.
Workbook 2 is a Master contract listing book. In column C is a listing of all contract names in effect (>2000). What I would like to do in each cell in column N of the Master contract listing book is multiply the respective quantity in Workbook 1 that has the same in both Workbook 1 and Workbook 2, by .75. In other words, if if row 2 in column F in Workbook 1 is "New York", and column H has 90, I would like a formula in column N of Workbook 2 that would multiply the 90 by .75 in the cell in the row where "New York" appears in column C of Workbook 2. Any ideas?
Thanks in advance.

20091109, 10:32 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
You write workbook 1 and 2  did you mean worksheet?
I assume that row 1 contains column headings, and that the data start in row 2.
In cell N2 in "Workbook 2", enter the following formula:
=IF(ISERROR(VLOOKUP(C2,'Workbook 1'!$F$2:$H$200,3,FALSE)),"",VLOOKUP(C2,'Workbook 1'!$F$2:$H$200,3,FALSE)*0.75)
If you really meant workbooks, you need to specify both the workbook and worksheet names:
=IF(ISERROR(VLOOKUP(C2,'[Workbook 1.xls]Sheet1'!$F$2:$H$200,3,FALSE)),"",VLOOKUP(C2,'[Workbook 1.xls]Sheet1'!$F$2:$H$200,3,FALSE)*0.75)
If you prefer to return 0 instead of an empty string if there is no match, replace "" with 0 in the formula.

20091109, 10:49 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Thanks Hans. I meant workbook. One additional fact I failed to mention is that in workbook 1, there could be repeats of contract numbers and names. Therefore I would need a formula that would sum all of the same names to the cell in workbook 2. I'm not sure your formula does this...
Also, I don't see the multiplication by .75

20091109, 10:55 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
The VLOOKUP formula doesn't do that, because you explicitly stated in your original question that you wanted to look up a single value.
You can use a SUMPRODUCT formula:
=SUMPRODUCT(('[Workbook 1.xls]Sheet1'!$F$2:$F$200=C2)*'[Workbook 1.xls]Sheet1'!$H$2:$H$200)*0.75