Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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")))

share|improve this answer
    
Thanks a lot, it worked charmingly correct...But I used the lenghty formula. Please elaborate on creating sheetlist –  roman_bull Jan 2 '13 at 10:41
    
"right-click" anywhere on the worksheet and select "Name a range" - in the "name" box type the name - e.g. Sheetlist and in the "refers to" box paste in the list with = at the start, i.e. ={"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"} - it's also possible to type the sheet names into any range on the worksheet, e.g. G1:G12 and then just type that range into the "refers to" box –  barry houdini Jan 2 '13 at 10:51
    
Thanks a lot it was of great help!!!! –  roman_bull Jan 2 '13 at 11:21
    
Very nice, @barry. Always something to learn! –  chuff Jan 2 '13 at 22:24

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:

 =sum(iferror(vlookup(<person name>,<January sheet name!data range>,<leave column number>,0),0),
      iferror(vlookup(<person name>,<February sheet name!data range>,<leave column number>,0),0),
      etc.
      )

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.

share|improve this answer
    
I tried the above example and it works fine if all months have leave column filled. If any one month is blank it is give #value!. Following is the example of the formula =SUM(IFERROR(VLOOKUP(B8,Jan!B8:JD110,37,0),0),IFERROR(VLOOKUP(B8,Feb!B8:JD110,37‌​,FALSE),0),IFERROR(VLOOKUP(B8,Mar!B8:JD110,37,0),0)) –  roman_bull Jan 2 '13 at 9:27
    
Could you check whether any of the blank leave cells actually have spaces in them? –  chuff Jan 2 '13 at 22:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.