Trying to understand total share of wallet for the same client (s) with multiple products

[RichieFleur]

Hi all,

My first post so please excuse if I don't yet speak the lingo as well as some!

I am trying to manage my entire client book. I have a manual report which shows client name and surname in columns A and B. Many clients repeatedly purchase several and different products from us, listed in columns C (brand of product) and D (actual product name). Spend per annum is in column E.

1. I am wanting to do a client count for columns A & B but more importantly, 2. I want to be able to view each clients total spend pa, as such, all the totals of their purchases in column E. I need both of these automatically calculated for me.

As I am writing this, it all sounds woefully inefficient - if you have better ideas, please do let me know. My data is imported in different forms in csv. so has ended in a collective sheet that is largely imperfect at the moment.

Thanks
Richard

[Richard Buttrey]

It entirely depends as to exactly how your data is laid out. Rather than guess why not upload the workbook so that we can better understand it

[RichieFleur]

I uploaded a file, but cant see it. Suggestions?

[JeteMc]

Hello RichieFleur and Welcome to Excel Forum.
I imagine that you tried to use the paperclip icon to upload the file, unfortunately it doesn't work.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[RichieFleur]

Done I think, let me know?

[JeteMc]

This proposed solution employs a helper column (C) which may be hidden for aesthetic purposes.
The formula that populates the helper column is: =A2&" "&B2
The formula that populates the list of clients in column I is:

Please Login or Register  to view this content.

The formula that yields the total of purchases for each client is: =IF(I2="","",SUMIF(C$2:C$13,I2,F$2:F$13))
The formula that yields the number of clients is: =COUNT(J2:J13)
Let us know if you have any questions.