Desperate Help needed with a function.

[Guest]

I am an average excel user and cannot figure out if this function is possible.

I have a data list with company name, contact name, address, city, state,
zip, and sales rep. It has over 10,000 names. I need to find out how many
companies in each state then subtotal it by each city. I also want the city
name to appear with the total without having to type it in for each one.

This would be used for the sales person to know how many companies are in a
particular city.

Thank you.

 

[Anne Troy]

Hi, Lori. You need the subtotals feature. You'll just choose COUNT instead
of SUM. Sort first by State, then by City. See this:
http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

Thank you so much. I think this will work perfectly!

 

[Debra Dalgleish]

You can use a pivot table to summarize the date by state, and city.
There are instructions in Excel's Help, and Jon Peltier has information
and links:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Add State and City to the row area, and Company to the data area, where
it will become Count of Company.
Add Sales person to the Page area, and you can see just the companies
assigned to that sales rep.