If the value matches, then sum values from multiple sheets for that value in excel

Question

I have 3 sheets(Sheet1, Sheet2, Sheet3) in my excel.

                                           SHEET 1  
Name    OUTPUT EXPECTED
        IF(Name in sheet 1 matches name in sheet 2 and sheet 3, 
         then sum V1 of Sheet 2 and Sheet 3 for that name)
A        12
B        11
C        5
D        4


                                           SHEET 2  
Name    V1
A       5
B       5
C       3
D       2


                                          SHEET 3   
Name    V1
 A      7
 B      6
 C      2
 D      2

If the value of Column A in Sheet1 matches the value of Column A in Sheet2 and Sheet3, then sum the values of another column in sheet2 and sheet3 for that matching value.

Now, I am using the below formula which gives me the correct result. But when I change the order of the values in Column A of sheet1, the formula doesn't work correctly.

=SUM(Sheet2!B2,Sheet3!B2)
=SUM(Sheet2!B3,Sheet3!B3)
=SUM(Sheet2!B4,Sheet3!B4)
=SUM(Sheet2!B5,Sheet3!B5)

I am new to writing a formula in Excel, I read that I need to use VLOOKUP for achieving this, but really don't understand and get it as it says that the order has to be in Ascending order for VLOOKUP to work fine. (Column A is not going to be sorted in Ascending order)

Any help and suggestions in writing the formula, please!

TIA.

JvdV JvdV · Accepted Answer · 2018-05-30T08:03:39

Use sumif instead if you want to add the values from sheet1, sheet2 and sheet3 altogheter:

=SUMIF(A:A,A1,B:B)+SUMIF(Sheet2!A:A,Sheet1!A1,Sheet2!B:B)+SUMIF(Sheet3!A:A,Sheet1!A1,Sheet3!B:B)

This will add the values from the three sheets, regardless if the value from sheet1 actually excists on the other ones

If the value matches, then sum values from multiple sheets for that value in excel

2 Answers