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.

Lori wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html