[Solved] Formulas: adding cells if other cells match...

    I have 2 spreadsheets that have data re. numerous people. Sometimes these people appear on both sheets. What I would love to do is have a formula that will compare the names and if they are the same, add the totals from each sheet for that person. :wow: Is this even possible?
    The names on both lists can change everyweek, and since they are arranged by SS number, they will not be in the same rows from week to week, so I cannot match them by cell or row, I need excel to match the names (in column A of both sheets) and if/when it finds a match, add the totals for each person (cells in column H of the same rows)
    Is this possible?
    Thank you in advance for any ideas
    :tumble:

    In sheet 3 A1, where you would want to indicate the number of times a name occurs, type this simple formula:=COUNTIF(Sheet1!A1:A5, Sheet3!C1) + COUNTIF(Sheet2!A1:A5, Sheet3!C1).</n&gt;
    Where Sheet 1 A1~A5 & Sheet 2 A1~A5 contains the names of people and Sheet 3 C1 allows you to type in who evers name you wanna see. You can then manipulate the formula variables to suit your needs.:) Hope it helps!

    Where do you want to place this total & will the names only occur once in each list?


    I will assume the totalo should be in column I of the first sheet with names.


    In I2 of sheet 1 type
    =IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$16,0)),0,VLOOKUP(Sheet1!A2,Sheet2!$A$2:$H$16,8,FALSE))


    This will place the value from Col H sheet2 in Col I sheet1 if there is a colA match in sheet 2


    Hope this helps...


    Will

    Hi Guys!! Sorry I took so long to reply, have been just overwhelmed with work (and underwhelmed with ideas)
    I tried both fomulas. Will, I have yours on the spreadsheet now, but I get a #N/A
    error. What does that mean?
    It only appears in the cells that do have matching names, the others show 0.


    Yes the names occur only once in each list.
    I changed your formula to fit my sheet...or thought I had...in example,


    =IF(ISNA(MATCH(A5,'BACK SERVER'!$A$5:$A$72,0)),0,VLOOKUP('FRONT SERVER'!A5,'BACK SERVER'!$N$5:$N$72,8,FALSE))


    Since my original inquiry, I have added more information in each row so the totals are now in column N on sheet 2(backserver)and I am placing this fomula in columnQ of sheet one(frontserver!).
    Do I have something backwards??


    Thanks for your help, both of you, you are awesome!

    try this


    =IF(ISNA(MATCH(A5,'BACK SERVER'!$A$5:$A$72,0)),0,VLOOKUP(A5,'BACK SERVER'!$a$5:$N$72,8,0))


    check the 8 at the end of the formula - it is supposed to refer to the column number in the range you want the data from - if you have added columns this may now longer be valid.


    hth

    There are three types of people in this world.
    Those who can count and those who can&#039;t.

    Thank You Neale! That works Perfect!
    That will save me a lot of time!
    benlauhh - When I saw your formula, I realized it wouldn't quite work here, but I used it on a different sheet and it worked great :)


    Thanks to all three of you for your help!:spin::biggrin:

Participate now!

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

Register Yourself Login