If, Vlookup & Sum

    Hi all, Im a newcomer to the site, have been in the background for a while and have got some great help and tips from this forum, but now I have a question that I can't find an answer to.


    I have 2 worksheets, 1 & 2.
    Number 1 has a list of clients i.e tom, john, paul and each client has several different values at different dates i.e
    Tom - 15
    Tom - 20
    John - 30
    John - 35 etc.


    On worksheet 2 I have the full list of clients in the first column and on the second column I need to have the total for each client from worksheet number 1.
    Can anyone advice a formula I could use in worksheet 2 that will lookup the client name in cell A1 and return the total for that client from worksheet 1?
    Many thanks.

    Re: If, Vlookup & Sum


    Welcome to the forum!


    Use Sumif for something like this -


    =SUMIF(Sheet1!A1:B4,Sheet2!A1,Sheet1!B1:B4)


    This assumes your sample data above is in A1 to B4 (i.e. on sheet 1, 4 names in Column A with 4 values in column B). Also assumes that "Tom" is listed in A1 of sheet 2.


    Put that formula into Sheet2, cell B1.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

    Re: If, Vlookup & Sum


    Hi


    Welcome to the forum


    enter this formula in the cell on worksheet2 next to the client name


    =SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)


    where A1:A12 is the range of clints and C1:C12 is the range containing the various values


    HTH


    Robert


    ps sorry to appear to have duplicated Ger's solution, my phone rang before I could send


    R

    Re: If, Vlookup & Sum


    Hi


    Welcome to the forum


    enter this formula in the cell on worksheet2 next to the client name


    =SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)


    where A1:A12 is the range of clints and C1:C12 is the range containing the various values


    HTH


    Robert

    Re: If, Vlookup & Sum


    though you may find this variation easier to copy down:
    [frc]=SUMIF(Sheet1!$A$1:$B$4,Sheet2!A1,Sheet1!$B$1:$B$4)[/frc]
    just adds absolute refs where appropriate.


    p45cal

    Re: If, Vlookup & Sum


    Good call P45Cal.

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login