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.
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
You can shorten that by using a named range, e.g.
then formula becomes
This summary will be much easier to accomplish if you consolidate your monthly sheets into a single database sheet for the year. Then you can set up a Pivot Table that will do the summing up by person in short order.
I suspect your sum of vlookups is throwing an error because each person takes leave in only a few of the months in a year. The way to deal with this is to wrap the lookups in IFERROR functions. So the formulas would have to look like:
This could be made slightly less awkward by setting up a lookup for each month in individual columns and then summing the results across columns.
Another alternative which I'm not very familiar with--but that might meet your needs--is the Data Consolidate command. An explanation of the command can be found here.
I highly recommend the Pivot Table approach.