pivot table - count of different values

I have a sheet that is set up with these columns:

There are 4 different sales categories that a client can purchase products from. Some clients purchase multiple products from the same category.

I need to report on the average # of sales categories each client buys from. So while a client could have 6 rows each indicating SalesCategory "A" I want it to show me that they only buy from 1 category. So the Max # that should show up in my report would be 4.

Because the clients vary and are added to monthly, I thought a Pivot Table would be the way to do this, but it's counting every product the client buys...so in the example above, the client is showing a 6 under SalesCategory A.

How can I get this to just show me how many DIFFERENT items in SalesCategory a client buys from?

I've attached an example.
Sample Product per Client.xlsx

I'm not sure I'm understanding. I removed the Grand Totals, but I had already had a count function in the pivot table and it's still showing me, for example, Client A has 5 products under SalesCategoryA but I only want it to show 1.

I added a column called SalesCategoryCount to your data and used this formula

Please Login or Register  to view this content.

starting in cell D1 and copying down. It works for 100 rows...you can adjust formula to as many rows as needed.

Once this is here, you can use the sum of the SalesCategoryCount data in your pivot table to get the info to look like you wanted.

Thank you!

To translate my formula to R1C1, and assuming that our first formula is in Row 2, Column 4, with Column 4 being SalesCategoryCount, then this code should work, copied from Row 2, Column 4 downwards.

Please Login or Register  to view this content.

Hmm it's still not working, but I think it's an issue in my VBA...I'll post in that forum. Thanks!!