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(&lt;person name&gt;,&lt;January sheet name!data range&gt;,&lt;leave column number&gt;,0),0),
      iferror(vlookup(&lt;person name&gt;,&lt;February sheet name!data range&gt;,&lt;leave column number&gt;,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.

Sum of vlookup values on multiple sheets
Given the following situation:

Within our company's farm the creation of MySites / PersonalSites is limited to members of a special AD Group ("MysiteCreator").

This group has been given the following permissions within the User Profile Service:



All the other, "casual" users are not permitted to create a MySite / Personal Site.

Now I'm wondering how to check by code if the current user is permitted to create a personal site and then create it on demand.

I've read some posts about checking the UserProfile.PersonalSiteCapabilities == "14" (see here and here). So I wrote this piece of code:

internal bool EnsurePersonalSite()
{
  UserProfileManager upm = new UserProfileManager(SPServiceContext.Current);
  UserProfile up = upm.GetUserProfile(true);

  // User not permitted (?) to create personal site
  if ((up.PersonalSiteCapabilities &amp; PersonalSiteCapabilities.Social)
    != PersonalSiteCapabilities.Social)
  {
    return false;
  }

  // User permitted ==&gt; create personal site
  up.CreatePersonalSiteEnque(true);
  return false;

  // Creation ongoing
  if (up.PersonalSiteInstantiationState != PersonalSiteInstantiationState.Created
    || up.PersonalSite == null)
  {
    return false;
  }

  return true;
}


The casual user has a PersonalSiteCapabilities == 0 entry in his user profile (shown in Central Administration). Adding the user to the "MysiteCreator" AD group does not change this value to 14 and my code will always return false.

If I would set PersonalSiteCapabilities = 14 for all users my code will always return true no matter if the user is member of the AD Group "MysiteCreator" or not.

So what's the best, safest way to ensure a MySite / Personal Site for users only belonging to a special AD group?
