Formula to Populate the Total Number of Data in Separate Workbook

**victoria07** · 12-23-2013, 05:19 PM

HELP!

I am working on a data list which includes store names, address, city, province, postal code, contact details, sales rep. I need the total number of stores, total stores per sales rep and total number of each city per sales rep.

For example:
Detailed Workbook has the following information:
McDonald's - 123 Ave - Toronto - Ontario - 416-555-5235 - Bill
McDonald's - 458 Street - Toronto - Ontario - 647-555-8942 - George
McDonald's - 45 Street - Niagara Falls - Ontario - 519-555-4579 - Jill
McDonald's - 24 Road - Niagara Falls - Ontario - 519-555-6347 - George
KFC - 124 Ave - Toronto - Ontario - 579-555-8521 - George
KFC - 1 Street - Niagara Falls - Ontario - 519-555-6663 - Jill

Summary Workbook would then total the number of stores:
McDonald's = 4
KFC = 2

total stores per sales rep:
Bill = Total Stores 1
George = Total Stores 3
Jill = Total Stores 2

and total number of each city per sales rep:
Bill
Toronto - 1

George
Toronto - 2
Niagara Falls - 1

Jill
Niagara Falls - 2

Is there a formula that will populate the totals from a Detailed Workbook into a Summary Workbook?

Any help will be very much appreciated!! If you have any tips of a better way to organize the information, this would also be great help!

Thank you.

**Kaper** · 12-23-2013, 05:27 PM

Your summarized information (and much more) would be easiest to obtain with Pivot Table - if you do not know this tool - start with tutorials published on Microsoft pages.
You will regret no single minute spent on learning basics of Pivot Tables.

**JBeaucaire** · 12-23-2013, 05:32 PM

You will have to organize your summary workbook, then construct the formulas while both workbooks are open. After that, once they are working, you will be able to close the reference workbook and the formulas will keep working.

THe function for summing a range of values on various criteria is SUMIF() for one criteria, and SUMIFS() for multiple criteria. This function only works of the referenced workbook is open. The alternate function you can use is SUMPRODUCT.