Yearly Summary Sheet

[mwise]

I have a spreadsheet with a worksheet for each month of the year. Each
worksheet contains a list of employees, the amount they contribute to their
401K, and what we match. I need to create a worksheet that shows how much
each employee contributed that year. Also, since employees come and go every
month. The list of employees is not always the same.

 

[JLatham]

There are a couple of ways to deal with this. The first way, a 3-dimensional
Sum(), would be the easiest.

You say your list can change from month to month based on employee turnover,
but I would ask this: don't you keep the list to include separated employees
so that you have a complete employee list for the year, even if they're not
all still with you?

If you DO, then the 3-D SUM() is easy to use. Let's say your sheets are
named Jan, Feb ... Nov, Dec. Your list of employee names is in column A on
each and their 401K contribution for the month in column B.

On the annual total sheet just copy the longest list of names into column A,
and put a formula like this into a column next to their name on that sheet
(assumes names start in row 2)
=SUM(Janec!B2)
That will give you a total for all cells B2 on all sheets from Jan to Dec
and includes all those in between. Then you just fill the formula down to
the end of the list of names.

The other way involves a very long SUMPRODUCT() formula. To make it easy,
we will assume that the longest list of names goes from row 2 down to row 384
on one of the sheets. Again Names are in A and contributions in B on each
monthly sheet. List of names on the annual sheet are in column A also. In a
cell on the same row with names on the annual sheet, enter a formula that
would look something like this:
=SUMPRODUCT(--(Jan!A2:A384=A2),--(Jan!B2:B384)) +
SUMPRODUCT(--(Feb!A2:A384=A2),--(Feb!B2:B384)) + ....more months formulas
..... _ SUMPRODUCT(--(Dec!A2:A384),--(Dec!B2:B384))

As you can see, that's a pretty long formula, but it'll work. You can get
away with using the longest name list for the ending row because when names
don't match, you'll get a zero contribution from that particular worksheet.

Hope this helps you with the problem.

 

[T. Valko]

You could use a 3D SUMIF...not very efficient, though!

Summay sheet A2:An = unique list of employee names

Monthly sheets named in the format mmm: Jan, Feb, Mar, Apr...Dec

Column A = employeee names
Column B = values to sum.

Enter this formula on the Summary sheet in cell B2:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:12"))*30,"mmm")&"!A:A"),A2,INDIRECT(TEXT(ROW(INDIRECT("1:12"))*30,"mmm")&"!B:B")))

Copy down as needed.

Note: All 12 monthly sheets must exist otherwise you'll get an error. If
some sheets don't exist yet change this portion:

"1:12"

That represents the 12 monthly sheets. If you only have sheets up to, say,
October, then change "1:12" to "1:10".