Need help creating a table and classifying values which appear more than once

[fori_gump]

Sellers	Type of good	Amount	Company
Dave Murray	News	99	BBC
Jack Anderson	Food	123	Sainsbury's
Dave Murray	Clothes	12222	Gucci
Bob Davis	Clothes	145	LV
Lisa Thompson	Food	553	Tesco
Jack Anderson	Tech	23	TEC
Lisa Thompson	Tech	99	Netflix
Dave Murray	Tech	448	AmazonPrime
Dave Murray	Clothes	5721	HugoBoss
Bob Davis	Food	1239	Volvic
Lisa Thompson	Cars	42923	Mercedes
Dave Murray	Cars	824	Nissan
Jack Anderson	Cars	1224	Fiat
Dave Murray	News	232	ITV
Dave Murray	Clothes	2222	HM
Dave Murray	Clothes	111	Balenciaga
Lisa Thompson	Cars	69	BMW
Lisa Thomson	Food	2222	Morrisons

I need to create a list which shows each seller and how many sales they have for each type of good, but for instance as Dave Murray sales clothes for two different companies, i would need his name to appear only once and show the total for all clothes he sells and so on. l I need the formula which can be used to do this.

 

[etaf]

A pivot table would do that for you very easily

Book6
	A	B	C	D	E	F	G	H	I
1	Sellers	Type of good	Amount	Company			Row Labels	Sum of Amount
2	Dave Murray	News	99	BBC			Bob Davis	1384
3	Jack Anderson	Food	123	Sainsbury's			Clothes	145
4	Dave Murray	Clothes	12222	Gucci			Food	1239
5	Bob Davis	Clothes	145	LV			Dave Murray	21879
6	Lisa Thompson	Food	553	Tesco			Cars	824
7	Jack Anderson	Tech	23	TEC			Clothes	20276
8	Lisa Thompson	Tech	99	Netflix			News	331
9	Dave Murray	Tech	448	AmazonPrime			Tech	448
10	Dave Murray	Clothes	5721	HugoBoss			Jack Anderson	1370
11	Bob Davis	Food	1239	Volvic			Cars	1224
12	Lisa Thompson	Cars	42923	Mercedes			Food	123
13	Dave Murray	Cars	824	Nissan			Tech	23
14	Jack Anderson	Cars	1224	Fiat			Lisa Thompson	43644
15	Dave Murray	News	232	ITV			Cars	42992
16	Dave Murray	Clothes	2222	HM			Food	553
17	Dave Murray	Clothes	111	Balenciaga			Tech	99
18	Lisa Thompson	Cars	69	BMW			Lisa Thomson	2222
19	Lisa Thomson	Food	2222	Morrisons			Food	2222
20							(blank)
21							(blank)
22							Grand Total	70499
23
24
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Sellers	Type of good	Amount	Company
2	Dave Murray	News	99	BBC		Dave Murray	News	331
3	Jack Anderson	Food	123	Sainsbury's		Jack Anderson	Food	123
4	Dave Murray	Clothes	12222	Gucci		Dave Murray	Clothes	20276
5	Bob Davis	Clothes	145	LV		Bob Davis	Clothes	145
6	Lisa Thompson	Food	553	Tesco		Lisa Thompson	Food	553
7	Jack Anderson	Tech	23	TEC		Jack Anderson	Tech	23
8	Lisa Thompson	Tech	99	Netflix		Lisa Thompson	Tech	99
9	Dave Murray	Tech	448	AmazonPrime		Dave Murray	Tech	448
10	Dave Murray	Clothes	5721	HugoBoss		Bob Davis	Food	1239
11	Bob Davis	Food	1239	Volvic		Lisa Thompson	Cars	42992
12	Lisa Thompson	Cars	42923	Mercedes		Dave Murray	Cars	824
13	Dave Murray	Cars	824	Nissan		Jack Anderson	Cars	1224
14	Jack Anderson	Cars	1224	Fiat		Lisa Thomson	Food	2222
15	Dave Murray	News	232	ITV
16	Dave Murray	Clothes	2222	HM
17	Dave Murray	Clothes	111	Balenciaga
18	Lisa Thompson	Cars	69	BMW
19	Lisa Thomson	Food	2222	Morrisons
Sheet1

Cell Formulas
Range	Formula
F2:G14	F2	=UNIQUE(A2:B19)
H2:H14	H2	=SUMIFS($C$2:$C$19,$A$2:$A$19,F2,$B$2:$B$19,G2)
Dynamic array formulas.

 

[fori_gump]

A pivot table would do that for you very easily

Book6
	A	B	C	D	E	F	G	H	I
1	Sellers	Type of good	Amount	Company			Row Labels	Sum of Amount
2	Dave Murray	News	99	BBC			Bob Davis	1384
3	Jack Anderson	Food	123	Sainsbury's			Clothes	145
4	Dave Murray	Clothes	12222	Gucci			Food	1239
5	Bob Davis	Clothes	145	LV			Dave Murray	21879
6	Lisa Thompson	Food	553	Tesco			Cars	824
7	Jack Anderson	Tech	23	TEC			Clothes	20276
8	Lisa Thompson	Tech	99	Netflix			News	331
9	Dave Murray	Tech	448	AmazonPrime			Tech	448
10	Dave Murray	Clothes	5721	HugoBoss			Jack Anderson	1370
11	Bob Davis	Food	1239	Volvic			Cars	1224
12	Lisa Thompson	Cars	42923	Mercedes			Food	123
13	Dave Murray	Cars	824	Nissan			Tech	23
14	Jack Anderson	Cars	1224	Fiat			Lisa Thompson	43644
15	Dave Murray	News	232	ITV			Cars	42992
16	Dave Murray	Clothes	2222	HM			Food	553
17	Dave Murray	Clothes	111	Balenciaga			Tech	99
18	Lisa Thompson	Cars	69	BMW			Lisa Thomson	2222
19	Lisa Thomson	Food	2222	Morrisons			Food	2222
20							(blank)
21							(blank)
22							Grand Total	70499
23
24
Sheet1

Thank I think a pivot table is best.If i now wanted to use the data from the pivot table for another task, for instance if these sellers were awarded bonuses for their sales. Assuming there is another table which shows the different thresholds for bonuses, so 1000-3000 sales = £1000 and 4000-10000= £3000 and so on, what formula could i use to allocate bonuses to all these salesman
?

 

[etaf]

you can use a calculated field to add that - BUT may need to hardcode the IF() SO need to know all the values and amounts
have a look at this video, very quick

 

[fori_gump]

you can use a calculated field to add that - BUT may need to hardcode the IF() SO need to know all the values and amounts
have a look at this video, very quick

Thank you so much!