Total multiple row values for multiple IDs

[rnomis]

This was difficult to describe in a single title.
Our new accounting system produces a monthly spreadsheet. Column C contains the Client name but only once. Column H contains the monthly spend for multiple product types

Example:
CLIENT PRODUCT NetValue
Client1 product 1 £100
Product2 £150
Product3 £1000
Client2 Product1 £300
Product3 £200
Client3 Product4 £25000
Client4 Product2 £2000

As you can see, some clients use different products and different numbers of products. Somehow, I need to total the spend for each client. Ideally in a new sheet which would show:
Client1 £1250
Client2 £500
Client3 £2500
Client4 £2000

This data is produced each month so I need to do this then calculate the cumulative spend over a 12 month period into a new sheet. I would really prefer a formula as our system has quite strict security with macros.

I hope this is clear and look forward to some help. Thanks is advance
Simon

 

[Fluff]

Select col C > Ctrl G > Blanks > = & uparrow > Ctrl enter.
This will fill the client name down & you can then use normal formulae

 

[rnomis]

Thanks for your help. Unfortunately, I now discover that the supposedly blank cells are not actually blank. I discovered this when your reply didn't work. I selected some of the blank cells and clicked delete ad then tried again and just those cells I had deleted were selected. I don't know what is in these cells. Is there a way to delete the contents of these blank cells?
Thanks

 

[rnomis]

Thanks for your help. Unfortunately, I now discover that the supposedly blank cells are not actually blank. I discovered this when your reply didn't work. I selected some of the blank cells and clicked delete ad then tried again and just those cells I had deleted were selected. I don't know what is in these cells. Is there a way to delete the contents of these blank cells?
Thanks

OK, I found a way of finding all non blank cells using 'find and matching cell contents' and deleted them. A bit long winded but not too hard to run each month. Unless you know a different way?
Thanks anyway

 

[Peter_SSs]

OK, I found a way of finding all non blank cells using 'find and matching cell contents' and deleted them.

I'm not sure what you found. If the following is not sufficient, perhaps you can add more detail. For now, I have assumed that those 'blank' cells contained no more than 3 characters and that your client names all contain more than 3 characters.

Each formula copied down to the end of the column C data. The helper column could be hidden once populated.

Excel Workbook
	A	B	C	D	E	F	G
1	CLIENT	PRODUCT	NetValue		Idx	Client	Value
2	Client1	product 1	100		1	Client1	1250
3		Product2	150		4	Client2	500
4		Product3	1000		6	Client3	25000
5	Client2	Product1	300		7	Client4	2000
6		Product3	200
7	Client3	Product4	25000
8	Client4	Product2	2000
9
Sum per client

 

[rnomis]

I'm not sure what you found. If the following is not sufficient, perhaps you can add more detail. For now, I have assumed that those 'blank' cells contained no more than 3 characters and that your client names all contain more than 3 characters.

Each formula copied down to the end of the column C data. The helper column could be hidden once populated.

Excel Workbook
	A	B	C	D	E	F	G
1	CLIENT	PRODUCT	NetValue		Idx	Client	Value
2	Client1	product 1	100		1	Client1	1250
3		Product2	150		4	Client2	500
4		Product3	1000		6	Client3	25000
5	Client2	Product1	300		7	Client4	2000
6		Product3	200
7	Client3	Product4	25000
8	Client4	Product2	2000
9
Sum per client

Great, this is just what I was looking for. I can now expand this to include other data. Many thanks for your help.
Simon

 

[Peter_SSs]

Great, this is just what I was looking for. I can now expand this to include other data. Many thanks for your help.
Simon

You're welcome. Glad it worked for you.