Excel – Sum of vlookup values on multiple sheets

microsoft excelworksheet-function

Have 12 excel Sheets in a workbook for each month Jan-Dec. Each sheet contains Names and leaves marked for that month in the same row and sum value for each category of leave at the end of the table. I need to summarize these individual sheets value by looking for each person's Name in all sheets. how can I achieve this in excel…. I tried sum of vlookup from all sheet, it throws error or no result.

Best Answer

It may be simpler using SUMIF here because SUMIF won't give an error if the person isn't found on any particular sheet (although all sheets need to exist), so if you have sheets "jan" through to "dec" try this formula for the 12 month total

=SUM(SUMIF(INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!B8:B110"),B8,INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}&"!AL8:AL110")))

You can shorten that by using a named range, e.g.

Sheetlist ={"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"}

then formula becomes

=SUMPRODUCT(SUMIF(INDIRECT(Sheetlist&"!B8:B110"),B8,INDIRECT(Sheetlist&"!AL8:AL110")))

Related Question