Results 1 to 4 of 4

Thread: Sumproduct (?)

  1. #1
    Bronze Lounger
    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 75-100 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.


  2. #2
    Plutonium Lounger
    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.

  3. #3
    Bronze Lounger
    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

  4. #4
    Plutonium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •