How can I summarize non unique information?

[tlmf2222]

Good morning all! I'm having some difficulty summarizing non unique information. My sheet of data is set up similar to the following

Customer Name Licenses Size Case #
ABC Plumbing 10 123456
ABC Plumbing 10 132131
ABC Plumbing 10 231233
Barb Electronics 9 231143
Z Industries 4 209343
Z Industries 4 231033
etc...

There are a total of about 45,000 rows and as you can see, the unique item is the case #, but the customer name and the associated license size can be repeated. What I am looking to do is summarize the information in the following format (or similar):

License Size Customer Count Total Cases
1-5 13 4000
6-10 27 7838
etc....

I think a pivot table is the way to go, but the problem I am finding is that I can't get the unique customer count, it always just sums the total customers seen, such that I get the same number of customers as I do cases. I don't see a "distinct count" within the field options in a pivot table.

Any ideas?

Thanks!

 

[JulieG]

Two things i would do is...

- Conditional formatting > Highlight rules > Highlight Cell that contain Text. > Enter company name.

- Also, Use "Find & Replace".. Under Find enter company name and select find all.

 

[Dougie1]

Double click where it says Sum of Customers on your Pivot Table and change it from Sum to Count

 

[tlmf2222]

When I drag the "Customer Name" field into the pivot table, it starts out as "Sum of Customers" and all the values are zero. But, when I change the Sum to be a Count, it counts every instance of the customer, even if the customer name is listed over and over again. So, the customer count ends up being the same number as the total number of cases (about 45,000). I only want to count the unique instances of each customer name.

 

[JulieG]

Try right clicking on the customer name that you want to total and select filter. Then click on keep only selected items. From that point you can just highlight the selection and that will give you a count at the bottom of your screen.

 

[tlmf2222]

I'm not sure if I understand how that would work. My pivot table looks like this:

License Group Count of Case # Count of Customer
1-5 10869 10869
6-10 507 507
11-15 6959 6959
etc...

I want the "Customer of Customer" to count only the unique. Are you saying that I should just filter my source data? That would only change the display of the information on the screen, the data would still be there and calculate into my totals, regardless of whether it showed on the screen.

 

[JulieG]

No.. What i spoke of is only a way to count a specific customer. It will not be in your pivot table. Its just a way for you to know.

I also found this information. It should help you. Let me know if you have any problems?
http://www.ozgrid.com/Excel/count-list.htm

 

[tlmf2222]

Thanks Julie for the help. I actually did know how to do the count just for unique. I guess I'm trying to get it all done in one spot, since having to do it piece by piece is taking too long.