If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula to sum up customers totals from other sheets
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... --- Steve |
#2
|
|||
|
|||
Formula to sum up customers totals from other sheets
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 "Steve" wrote in message ... 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... --- Steve |
#3
|
|||
|
|||
Formula to sum up customers totals from other sheets
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 -- __________________________________ HTH Bob "Steve" wrote in message ... 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... --- Steve |
Thread Tools | |
Display Modes | |
|
|