|
Excel Formula
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 |
Excel Formula
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 "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 |
Excel Formula
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. |
Excel Formula
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 B1:P1. 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. "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 |
| All times are GMT +1. The time now is 03:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com