sumIf or lookup?

I have a workbook with 20 identical worksheets. Each sheet records for one person, a column for number of units processed in that week, number of hours worked, and the focus area for that person (one of three values) for that person during that week.

In another worksheet in the same workbook, I want to sum he totals for each week, but I want to narrow the totals by one of the three focus areas. So, I have a line for each focus area to total only those values across the 20 sheets with that focus area. For example,

Across the columns would be:
Total Units Week 1 | Total Units Week 2 | Total Units Week Three

Down the rows would be:
Focus area 1
Focus Area 2
Focus Area Three

The values in the crossing cells would be summed from all 20 sheets, but only if the focus area in the individual sheet for that week matches the Focus area the row indicates. I'm struggling with how to get this calculation across so many sheets. Any advice? Thanks in advance.

Hi,

Can you upload the workbook so we can see the requirement in its context.

Rgds

One option is to use a Pivottable. Pivotttables can consolidate several sheets.
See encl. file, or these links: Pivottables, and Consolidate Pivotables.

Hope this helped
Ola

I might be wrong, but as I read it you just want a total on the last page of all the employee pages for certain criteria....this may help


=SUM(Sheet1!A1+Sheet2!A1+Sheet3!A1+Sheet4!A1+Sheet5!A1)

This is how you would sum all the A1 cells over several sheets, use this in a cell on your last page to total whatever criteria you need.

Hope this helped...

Ryk