summarize data from multiple worksheets
 

I have a workbook with 33 sheets representing 33 pay periods. I have a list
of 3900 employees who were paid during the year. How can I summarize the
data by employee to get a total for the year? Not every employee was paid
every pay date.

summarize data from multiple worksheets
 

That depends on how the data is set out on each sheet. But I would begin
this way:
1) On summary sheet in column A, enter names of all employees
2) Use a SUMPRODUCT formula to sum all the 33 sheets for each employer
Gives us more details and we can be more specific
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cdshon" wrote in message
...
I have a workbook with 33 sheets representing 33 pay periods. I have a
list
of 3900 employees who were paid during the year. How can I summarize the
data by employee to get a total for the year? Not every employee was paid
every pay date.

summarize data from multiple worksheets
 

Sumproduct can only contain up to 30 array references...

"Bernard Liengme" wrote in message
...
That depends on how the data is set out on each sheet. But I would begin
this way:
1) On summary sheet in column A, enter names of all employees
2) Use a SUMPRODUCT formula to sum all the 33 sheets for each employer
Gives us more details and we can be more specific
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cdshon" wrote in message
...
I have a workbook with 33 sheets representing 33 pay periods. I have a
list
of 3900 employees who were paid during the year. How can I summarize
the
data by employee to get a total for the year? Not every employee was
paid
every pay date.

summarize data from multiple worksheets
 

You misunderstand this 30 limit
Excel allows only 30 arguments
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marc" wrote in message
...
Sumproduct can only contain up to 30 array references...

"Bernard Liengme" wrote in message
...
That depends on how the data is set out on each sheet. But I would begin
this way:
1) On summary sheet in column A, enter names of all employees
2) Use a SUMPRODUCT formula to sum all the 33 sheets for each employer
Gives us more details and we can be more specific
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cdshon" wrote in message
...
I have a workbook with 33 sheets representing 33 pay periods. I have a
list
of 3900 employees who were paid during the year. How can I summarize
the
data by employee to get a total for the year? Not every employee was
paid
every pay date.

summarize data from multiple worksheets
 

you could use a Pivot Table with mutiple consolidation ranges (i.e. your 33
tabs)

Or the very manual approach you could put (copy/paste) all 33 periods data
into one tab having the columns labeled and sort the data by employee, then
use 'Data -Subtotals' to provide totals by each change in employee
identifier, using the Sum function - good luck

"cdshon" wrote:

I have a workbook with 33 sheets representing 33 pay periods. I have a list
of 3900 employees who were paid during the year. How can I summarize the
data by employee to get a total for the year? Not every employee was paid
every pay date.

summarize data from multiple worksheets
 

And he's got a file with 33 sheets, one for each pay period. So you can't
sum all periods in one formula, because you'd need to refer to each of the
33 sheets.

Unless he consolidates pay periods, or breaks up the summation into multiple
sumproducts... you can't use a single formula that references all 33 sheets.

"Bernard Liengme" wrote in message
...
You misunderstand this 30 limit
Excel allows only 30 arguments
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Marc" wrote in message
...
Sumproduct can only contain up to 30 array references...

"Bernard Liengme" wrote in message
...
That depends on how the data is set out on each sheet. But I would
begin
this way:
1) On summary sheet in column A, enter names of all employees
2) Use a SUMPRODUCT formula to sum all the 33 sheets for each employer
Gives us more details and we can be more specific
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cdshon" wrote in message
...
I have a workbook with 33 sheets representing 33 pay periods. I have
a
list
of 3900 employees who were paid during the year. How can I summarize
the
data by employee to get a total for the year? Not every employee was
paid
every pay date.

summarize data from multiple worksheets
 

I would have also suggested a pivot table, and have done so in other posts.
But it seems like everyone are so hellbent on using SUMPRODUCT for all
conceivable problems that it would only fall on deaf ears...

"Smilingout_loud" wrote in
message ...
you could use a Pivot Table with mutiple consolidation ranges (i.e. your
33
tabs)

Or the very manual approach you could put (copy/paste) all 33 periods data
into one tab having the columns labeled and sort the data by employee,
then
use 'Data -Subtotals' to provide totals by each change in employee
identifier, using the Sum function - good luck

"cdshon" wrote:

I have a workbook with 33 sheets representing 33 pay periods. I have a
list
of 3900 employees who were paid during the year. How can I summarize
the
data by employee to get a total for the year? Not every employee was
paid
every pay date.