How to combine a vlookup with a sumif function!!!

Discussion in 'Microsoft Excel Misc' started by Mark the Shark, Apr 6, 2005.

  1. I've been trying to combine a vlookup function with a sumif function
    for weeks now, however, without success.

    This is my problem. Instead of looking up a single value, i.e.
    =vlookup(A1, rang, 4, false), I like to do this for an entire range
    and suming up that range. Something like this:
    1 2 3 4 5 6
    Table 1 10€ 35€ 50€ 23€ 60€ 50€
    Jack 10€ 35€ 50€ 23€ 60€ 50€
    Brian 10€ 35€ 50€ 23€ 60€ 50€
    Juergen 10€ 35€ 50€ 23€ 60€ 50€
    Feff 10€ 35€ 50€ 23€ 60€ 50€

    So, I like to look-up the sum of these value without suming up these
    values in table 1 and doing a vlookup on the sum.

    Table 2

    Feff
    Juergen
    Jack

    I think the formula should look something like
    this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
    really have no idea how excel can choose the particular row to sum up
    the value. I hope this is not too confusing to you. I figured I'd
    write it in English rather than German as more user can help me out on
    this. I really appreciate any help. thanks you!!!
     
    Mark the Shark, Apr 6, 2005
    #1
    1. Advertisements

  2. Mark the Shark

    Guest Guest

    If you know how many columns wide the data is, then it a SUM will work for you

    Let's assume row 1 has column headings and the data starts in row 2. Your
    example shows 1 column of names & 6 columns of data. How about this formula

    =SUM(OFFSET(B1:G1,MATCH("Jack",A2:A6,0),0,1,6))


    "Mark the Shark" wrote:

    > I've been trying to combine a vlookup function with a sumif function
    > for weeks now, however, without success.
    >
    > This is my problem. Instead of looking up a single value, i.e.
    > =vlookup(A1, rang, 4, false), I like to do this for an entire range
    > and suming up that range. Something like this:
    > 1 2 3 4 5 6
    > Table 1 10€ 35€ 50€ 23€ 60€ 50€
    > Jack 10€ 35€ 50€ 23€ 60€ 50€
    > Brian 10€ 35€ 50€ 23€ 60€ 50€
    > Juergen 10€ 35€ 50€ 23€ 60€ 50€
    > Feff 10€ 35€ 50€ 23€ 60€ 50€
    >
    > So, I like to look-up the sum of these value without suming up these
    > values in table 1 and doing a vlookup on the sum.
    >
    > Table 2
    >
    > Feff
    > Juergen
    > Jack
    >
    > I think the formula should look something like
    > this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
    > really have no idea how excel can choose the particular row to sum up
    > the value. I hope this is not too confusing to you. I figured I'd
    > write it in English rather than German as more user can help me out on
    > this. I really appreciate any help. thanks you!!!
    >
     
    Guest, Apr 6, 2005
    #2
    1. Advertisements

  3. Mark the Shark

    Domenic Guest

    Also...

    Table2!B1, copied down:

    =SUM(INDEX(Table1!$B$1:$G$5,MATCH(A1,Table1!$A$1:$A$5,0),0))

    Alternatively...

    Table2!B1, copied down:

    =SUM(VLOOKUP(A1,Table1!$A$1:$G$5,{2,3,4,5,6,7},0))

    OR

    =SUM(VLOOKUP(A1,Table1!$A$1:$G$5,ROW(INDIRECT("2:7")),0))

    Both of these need to be confirmed with CONTROL+SHIFT+ENTER, not just
    ENTER.

    Hope this helps!

    In article <>,
    (Mark the Shark) wrote:

    > I've been trying to combine a vlookup function with a sumif function
    > for weeks now, however, without success.
    >
    > This is my problem. Instead of looking up a single value, i.e.
    > =vlookup(A1, rang, 4, false), I like to do this for an entire range
    > and suming up that range. Something like this:
    > 1 2 3 4 5 6
    > Table 1 10€ 35€ 50€ 23€ 60€ 50€
    > Jack 10€ 35€ 50€ 23€ 60€ 50€
    > Brian 10€ 35€ 50€ 23€ 60€ 50€
    > Juergen 10€ 35€ 50€ 23€ 60€ 50€
    > Feff 10€ 35€ 50€ 23€ 60€ 50€
    >
    > So, I like to look-up the sum of these value without suming up these
    > values in table 1 and doing a vlookup on the sum.
    >
    > Table 2
    >
    > Feff
    > Juergen
    > Jack
    >
    > I think the formula should look something like
    > this...=(sum(if(vlookup(Table2'A1;range;1;false)=Table2'A1;sum(range)))...I
    > really have no idea how excel can choose the particular row to sum up
    > the value. I hope this is not too confusing to you. I figured I'd
    > write it in English rather than German as more user can help me out on
    > this. I really appreciate any help. thanks you!!!
     
    Domenic, Apr 6, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. moglione1

    Combine Sumif

    moglione1, Jul 18, 2006, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    198
    Guest
    Jul 18, 2006
  2. buffgirl71
    Replies:
    12
    Views:
    1,140
    buffgirl71
    Nov 14, 2006
  3. Guest

    How do i combine a lookup and a sumif formula?

    Guest, Jul 13, 2007, in forum: Microsoft Excel Misc
    Replies:
    8
    Views:
    633
    Roger Govier
    Jul 13, 2007
  4. mcmilja

    How to combine IF statement and Vlookup function?

    mcmilja, Jun 6, 2008, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    1,992
    ShaneDevenshire
    Jun 6, 2008
  5. brandon
    Replies:
    2
    Views:
    516
    Niek Otten
    Sep 17, 2008
Loading...

Share This Page