Excel Formula

[Newfie809]

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. Thank
----------------------------------------------------------------------------------------
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

 

[Duke Carey]

you should be able to simply create a pivot table off your data. select any
cell within the data, choose Data->Pivot table and follow the prompts in the
wizard

 

[HKaplan]

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.

 

[MrAcquire]

Sheet1 is your database with labels in row 1 and data in rows A2..D3000.

On Sheet2, list your 47 locations in A2:A48 and your 15 job descriptions in
B11. Then, in B2, enter the following formula.

=SUMPRODUCT(($A2=Sheet1!$A$2:$A$3000)*(Sheet2!B$1=Sheet1!$D$2:$D$3000)*(Sheet1!$C$2:$C$3000))

Copy it from B2 into all the cells through P48.