vlookup/sum if help
vlookup/sum if help
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vlookup/sum if help

  1. #1
    Guest

    Default

     
    Not sure of the best way to describe this, but im trying to create a sort of "sliding" vlookup and/or sumif formula. What I need is a formula that can accomplish the following: The first cell in a row (a3, for example) will report a value based on the lookup value in a2, my specified range, and the column index number 1 (for example). Then I need to be able to copy the formula over the next cell (b3), and it adds the lookup value from a2 again, except this time the column index is column 2, instead of column 1. Thats easy enough on its own, but the trick is, in addition to that, I need it to add in the lookup value from b2 also, with the column index number 1 applied to just his value.

    Following this, I will copy the forumla to cell c3, and it will add the lookup value in a2 with a column index of 3, the lookup value from b2 with a column index of 2, and the lookup value from c2, with a column index of 1. This repeats out to the right indefinetly.

    I suspect this is possible with some relatively simple vlookup and/or sumif combination. Just cant figure out what it is. Im new to this forum, so I appolgize is this has already been discussed. Thanks for your time.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    401
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you post some simplified inputs and expected outputs for the first couple of occurences. That would help.

  3. #3
    Guest

    Default

    No problem. Thanks for your help. I think is what you're looking for.
    col.A col.b col.c col.d
    row 1 30% 20% 15% 10%
    row 2 1000 2000 500 100
    row 3 300 800 700 530

    The formula im looking for is the one that goes in row 3. The output for cell a3 is 30% times 1000. The output for cell b3 is 30% times 2000 PLUS 20% times 1000. c3 is 30% times 500 PLUS 20% times 2000 PLUS 15% times 1000. And so on. I know I could write a massive series of if statements, but this is going to go out dozens of columns to the right, so I want to avoid that if at all possible. Let me know if you need any other information. Thanks again.

  4. #4
    Guest

    Default


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    401
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What you need is the equivalent of:
    SUMPRODUCT($A1:D1,D2:$A2) in cell D3. Unfortunately Excel won't accept the 2nd argument and converts the entry to
    SUMPRODUCT($A1:D1,$A2:D2).

    Anybody else have any ideas on how to reverse the order of $A2:D2?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-02-28 10:09, Anonymous wrote:
    No problem. Thanks for your help. I think is what you're looking for.
    col.A col.b col.c col.d
    row 1 30% 20% 15% 10%
    row 2 1000 2000 500 100
    row 3 300 800 700 530

    The formula im looking for is the one that goes in row 3. The output for cell a3 is 30% times 1000. The output for cell b3 is 30% times 2000 PLUS 20% times 1000. c3 is 30% times 500 PLUS 20% times 2000 PLUS 15% times 1000. And so on. I know I could write a massive series of if statements, but this is going to go out dozens of columns to the right, so I want to avoid that if at all possible. Let me know if you need any other information. Thanks again.
    Given that A1:C3 houses

    {0.3,0.2,0.15,0.1;
    1000,2000,500,100;
    300,800,700,530}

    the sample data you provided, along with the expected results in row 3,

    in C1 enter:

    =SUMPRODUCT($A$1:A1,N(OFFSET($A$2,0,COLUMN($A$2)+COLUMNS($A$2:A2)-COLUMN($A$2:A2)-1)))

    Drag this across as far as needed.

    My thanks to Scott R for requesting the sample data and intuiting that the first array must be multiplied by the reverse of the second array and also to Juan who had to deal with a sufficiently similar problem.

    Aladin


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com