Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data from multiple worksheets | Excel Discussion (Misc queries) | |||
Merging data from Multiple Worksheets | Excel Worksheet Functions | |||
Merging multiple worksheets into one, with ongoing data entry | Excel Discussion (Misc queries) | |||
Listing data from multiple worksheets | Excel Worksheet Functions | |||
Summarize data with multiple conditions | Excel Discussion (Misc queries) |