On Feb 4, 1:52*pm, Newfie809 wrote:
I am looking for a formuls that will be able to add all the FTE for each job
description at each location on sheet 1 and return the value to sheet 2. Hope
someone can help. *Thanks
---------------------------------------------------------------------------*-------------
Sheet 1
Location * * * * * * * * * *Employee Name * *FTE * * * * * *Job Desctiption
A1 * * * * * * * * * * * * * * B1 * * * * * * * * * * *C1 * * * * * * * *D1
Location 1 * * * * * * * * Name * * * * * * * * *1.00 * * * * * * Secretary
Location 1 * * * * * * * * Name * * * * * * * * *1.00 * * * * * * Secretary
Location 1 * * * * * * * * Name * * * * * * * * * *.75 * * * * * * Custodian
Location 1 * * * * * * * * Name * * * * * * * * * *.50 * * * * * * Custodian
Location 2 * * * * * * * * Name * * * * * * * * * 1.00 * * * * * *Secretary
Location 2 * * * * * * * * Name * * * * * * * * * 1.00 * * * * * *Secretart
Location 2 * * * * * * * * Name * * * * * * * * * * .25 * * * * * *Custodian
Location 2 * * * * * * * * Name * * * * * * * * * *.50 * * * * * *Custodian
Continues up to Row A 3000 with 47 Locations and at each Location there are
15 Job Descriptions.
-------------------------------------------------------------------------
Sheet 2
Location * * * * * Secretary * * * * *Custodian * * * * *
A1 * * * * * * * * * B1 * * * * * * * * * *C1
Location 1 * * * *2.0 * * * * * * * * * 1.25
Location 2 * * * *2.0 * * * * * * * * * * .75

Newfie

If you create a table in the summary worksheet with each combination
of location and job, then your formula couldlook something like this:

=SUMPRODUCT((location=A11)*(Description=B11)*FTE) where location is a
named range for the locations in your data table, description is the
named range for the job description column, and FTE is the FTE
column. In this example A11 would have a location, i.e. location 1.
B11 would have a job description, i.e. Custodian. The formula would
calc every match of location and description and return the total of
FTE's only for those records.