Search code examples
excelformulas

Extract Values from three sheets into final sheet based upon the there names

I have one workbook containing data in three sheets [Sheet1, Sheet2 and Sheet3] and fourth sheet is the main sheet.

I would like to add the values from three sheets into main sheet based upon the name if match. for example,

Sheet 1
A,B [columns]
test1,1000
test2,2000

Sheet 2
A,B [columns]
test2,1000
test1,2000

Sheet 3
A,B [columns]
test1,1000
test2,2000

Expected Results...

Main Sheet
test1, 4000
test2, 5000

Can you please suggest me the formula or any reference for this?

Solution

  • I think that's what you need - If I understood the input correctly, assuming you have Sheet1:Sheet3 in your workbook and SUM sheet which is "main" (formulas are for the last one):

    1. Type in A1: test1 or any value you want to summarize.
    2. Type in B1: =SUM((Sheet1!A:A=A1)*Sheet1!B:B,(Sheet2!A:A=A1)*Sheet2!B:B,(Sheet3!A:A=A1)*Sheet3!B:B), but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
    3. Add new values to sum up into A and autofill formula in B down as required.

    Sample file: https://www.dropbox.com/s/95n46kavpfj4xs7/Sum3Sheets.xlsx