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?
|