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

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

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

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?