Collecting data from two worksheets and copying to a summary

lngbrdguy

New Member
Joined
Oct 10, 2006
Messages
4
I have two worksheets in a workbook. Worksheet one is named CLAIMs, worksheet two is named UNITS. I am trying to compare how many units for each model were sold per month and how many service claims for each model unit were issued for the same month...trying to get a percentage of claims to sales.
I have the data in two different pivot tables but would like to put the totals next to each other in a summary sheet by month.
On the claims sheet each entry equals one service call. On the UNITS sheet a dealer may have purchased one or more units per entry.
make sense?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fawlty1

Board Regular
Joined
Aug 12, 2002
Messages
59
If I understand the question correctly, try using sumif formula. example:
Insert a new sheet into workbook and name it "Results", Label cell A1 "Model#", label cell B1 "Qty Purch". and label cell C1 "Claims".
List all Model#'s down column A.
In cell B2 enter: =SUMIF(UNITS!A:A,Results!A2,UNITS!B:B) & copy formula down.
This assumes that on sheet named UNITS, column A contains Model#'s & column B contains qty purchased.
Use the same formula for C2 except instead of UNITS Sheet use CLAIMS Sheet.
 

Forum statistics

Threads
1,141,757
Messages
5,708,349
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top