Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to total labor charges
I have a workbook, containing multiple sheets, and each sheet is named
according to an employee's name. Each employee's sheet contains a Customer name in column C, preceded by the total labor charge that the mechanic billed to that customer for each invoice generated in column A. Column B contains the total parts the mechanic billed to that customer, with each invoice on a separate row, just like the labor column. Column D contains the date of each invoice. I am trying to build a sheet which will give me the breakdown of each employee's total labor charged per customer, per day. Ex: Column A (Labor) Column B (Parts) Column C (Company) Column D (Date) $50.00 $25.00 Acme 11/1/2008 $75.00 $50.00 Pathmark 11/1/2008 $50.00 $25.00 Superfresh 11/1/2008 $75.00 $50.00 Acme 11/1/2008 $50.00 $25.00 Acme 11/2/2008 $75.00 $50.00 Pathmark 11/1/2008 $50.00 $25.00 Superfresh 11/1/2008 $75.00 $50.00 Pathmark 11/2/2008 Would return: Column A (Company) Column B (Date) Column C (Labor) Acme 11/1/2008 $125 Acme 11/2/2008 $50 Pathmark 11/1/2008 $150 Pathmark 11/2/2008 $75 Superfresh 11/1/2008 $100 Thanks in advance for any assistance... --- Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to total labor charges
select the sheet where u need the solution :
go to data | filter | advance filter | check copy to another location | list range Col C & Col D | copy to: give the range as A1 | check unique record only | ok | In cell C2 put this formula =SUMPRODUCT(--(Sheet2!A2=Sheet1!$C$2:$C $8)*(Sheet2!B2=Sheet1!$D$2:$D$8)*(Sheet1!$A$2:$A$8 )) On Nov 4, 8:02*am, "Steve" wrote: I have a workbook, containing multiple sheets, and each sheet is named according to an employee's name. Each employee's sheet contains a Customer name in column C, preceded by the total labor charge that the mechanic billed to that customer for each invoice generated in column A. Column B contains the total parts the mechanic billed to that customer, with each invoice on a separate row, just like the labor column. Column D contains the date of each invoice. I am trying to build a sheet which will give me the breakdown of each employee's total labor charged per customer, per day. Ex: Column A (Labor) * * * * *Column B (Parts) * * * * * Column C (Company) Column D (Date) $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Acme 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Superfresh 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Acme 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Acme 11/2/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Superfresh 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/2/2008 Would return: Column A (Company) * *Column B (Date) * * * * * *Column C (Labor) Acme * * * * * * * * * * * * * *11/1/2008 * * * * * * * * * * *$125 Acme * * * * * * * * * * * * * *11/2/2008 * * * * * * * * * * *$50 Pathmark * * * * * * * * * * *11/1/2008 * * * * * * * * * * *$150 Pathmark * * * * * * * * * * *11/2/2008 * * * * * * * * * * *$75 Superfresh * * * * * * * * * *11/1/2008 * * * * * * * * * * *$100 Thanks in advance for any assistance... --- Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to total labor charges
I have done as you instructed, but I get the error "The extract range has a
missing or illegal field name" when I select Col C & Col D together. ('Chris'!$C:$D) When I select Col C -hold down control key- then select Col D, I get the error "Database or list range is not valid" ('!$C:$C,'Chris'!$D:$D) Any thoughts? "muddan madhu" wrote in message ... select the sheet where u need the solution : go to data | filter | advance filter | check copy to another location | list range Col C & Col D | copy to: give the range as A1 | check unique record only | ok | In cell C2 put this formula =SUMPRODUCT(--(Sheet2!A2=Sheet1!$C$2:$C $8)*(Sheet2!B2=Sheet1!$D$2:$D$8)*(Sheet1!$A$2:$A$8 )) On Nov 4, 8:02 am, "Steve" wrote: I have a workbook, containing multiple sheets, and each sheet is named according to an employee's name. Each employee's sheet contains a Customer name in column C, preceded by the total labor charge that the mechanic billed to that customer for each invoice generated in column A. Column B contains the total parts the mechanic billed to that customer, with each invoice on a separate row, just like the labor column. Column D contains the date of each invoice. I am trying to build a sheet which will give me the breakdown of each employee's total labor charged per customer, per day. Ex: Column A (Labor) Column B (Parts) Column C (Company) Column D (Date) $50.00 $25.00 Acme 11/1/2008 $75.00 $50.00 Pathmark 11/1/2008 $50.00 $25.00 Superfresh 11/1/2008 $75.00 $50.00 Acme 11/1/2008 $50.00 $25.00 Acme 11/2/2008 $75.00 $50.00 Pathmark 11/1/2008 $50.00 $25.00 Superfresh 11/1/2008 $75.00 $50.00 Pathmark 11/2/2008 Would return: Column A (Company) Column B (Date) Column C (Labor) Acme 11/1/2008 $125 Acme 11/2/2008 $50 Pathmark 11/1/2008 $150 Pathmark 11/2/2008 $75 Superfresh 11/1/2008 $100 Thanks in advance for any assistance... --- Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to total labor charges
On Nov 4, 3:50*am, muddan madhu wrote:
select the sheet where u need the solution : go to data | filter | advance filter | check copy to another location | list range Col C & Col D | copy to: give the range as A1 | check unique record only | ok | In cell C2 put this formula =SUMPRODUCT(--(Sheet2!A2=Sheet1!$C$2:$C $8)*(Sheet2!B2=Sheet1!$D$2:$D$8)*(Sheet1!$A$2:$A$8 )) On Nov 4, 8:02*am, "Steve" wrote: I have a workbook, containing multiple sheets, and each sheet is named according to an employee's name. Each employee's sheet contains a Customer name in column C, preceded by the total labor charge that the mechanic billed to that customer for each invoice generated in column A. Column B contains the total parts the mechanic billed to that customer, with each invoice on a separate row, just like the labor column. Column D contains the date of each invoice. I am trying to build a sheet which will give me the breakdown of each employee's total labor charged per customer, per day. Ex: Column A (Labor) * * * * *Column B (Parts) * * * * * Column C (Company) Column D (Date) $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Acme 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Superfresh 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Acme 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Acme 11/2/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/1/2008 $50.00 * * * * * * * * * * * * *$25.00 * * * * * * * * * * * * *Superfresh 11/1/2008 $75.00 * * * * * * * * * * * * *$50.00 * * * * * * * * * * * * *Pathmark 11/2/2008 Would return: Column A (Company) * *Column B (Date) * * * * * *Column C (Labor) Acme * * * * * * * * * * * * * *11/1/2008 * * * * * * * * * * *$125 Acme * * * * * * * * * * * * * *11/2/2008 * * * * * * * * * * *$50 Pathmark * * * * * * * * * * *11/1/2008 * * * * * * * * * * *$150 Pathmark * * * * * * * * * * *11/2/2008 * * * * * * * * * * *$75 Superfresh * * * * * * * * * *11/1/2008 * * * * * * * * * * *$100 Thanks in advance for any assistance... --- Steve I have done as you instructed, but I get the error "The extract range has a missing or illegal field name" when I select Col C & Col D together. ('Chris'!$C:$D) When I select Col C -hold down control key- then select Col D, I get the error "Database or list range is not valid" ('!$C:$C,'Chris'!$D:$D) Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weekly labor total | Excel Worksheet Functions | |||
formula to calculate total rent charges | Excel Discussion (Misc queries) | |||
labor day holiday formula | Excel Discussion (Misc queries) | |||
Labor day formula | Excel Discussion (Misc queries) | |||
How do I automatically total and flag charges when they reach a s. | Excel Discussion (Misc queries) |