Formula to sum up customers totals from other sheets

[Steve]

I am trying to construct a formula which will add to a sheet called
"Totals" the amount of parts and labor a person billed to each
customer. The customer name is listed in column "A". The total labor
for that customer is listed in column "B". The total parts for that
customer is listed in column "C".There are 10 different customer
names, each placed consecutively in cells "A3" to "A12".

I have several different sheets, each titled with the mechanics name.
Each sheet belonging to a particular mechanic is identical to the
other. In column "A" there is the total amount of labor he invoiced,
per single bill. In column "B" there is the total amount of parts he
invoiced for that same bill. In column "C" there is the customer name.

Sheet "Bob"
A2(Labor) B2(Parts) C2(Cust. name)
$100 $50 Alpha
$125 $75 Bravo
$150 $100 Alpha
$50 $50 Charlie

Sheet "Tom"
A2(Labor) B2(Parts) C2(Cust. name)
$100 $50 Alpha
$125 $75 Bravo
$150 $100 Alpha

Sheet "Totals" >>>>>>>>Desired
A3(Cust. name) B3(Labor) C3(Parts)
Alpha $500 $300
Bravo $250 $150
Charlie $50 $50

So basically, Sheet "Totals" places the labor totals & parts totals
from the mechanics sheets according to each customer's name.

Thanks in advance for any assistance...

 

[Ashish Mathur]

Hi,

First of all, good question. Try this solution.

1. In a new sheet (say - consolidate) type Labor, parts and Cust. Name in
cells A2, B2 and C2;
2. Now while in cell A3 of the Consolidate sheet, Go to Data > Consolidate;
3. While in the reference box, give the range reference (A3:C12) of the
individual sheets separately I.e. if there are 10 sheets you will have to
give 10 references;
4. Now under the function drop down, select "Count";
5. Check the box for Create Linked to Source data;
6. You will now see Grouped data. Please click on 2 at the top left
corner;
7. You will notice that this exercise will aggregate data from the
different sheets;
8. Now you can simply prepare a pivot table which will sum up the data per
Cust. Name

Hope this helps.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bob Phillips]

Create a list of the mechanic's sheets in M1:Mn, and then use

=SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$2&"'!C:C"),$A3,INDIRECT("'"&$M$1:$M$2&"'!A:A")))

and

=SUMPRODUCT(SUMIF(INDIRECT("'"&$M$1:$M$2&"'!C:C"),$A3,INDIRECT("'"&$M$1:$M$2&"'!B:B")))

I have assumed just the two names in this example