Excel Questions

[jibu08]

hello everyone, URGENT HELP
need your help in finding a formula..
i have a very large database where i have more than 1000 product and 500 customer .. i need to know what products are listed/sold by each customer .. i have used count-if and count-ifs but it is giving me repeated product ... for eg: luna has 20 product so i need to know 111 customer is selling how many luna product?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer code[/TD]
[TD]Product code[/TD]
[TD]Brand[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10010040[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]Luna[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Luna Green Peas[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10010050[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]Luna[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Luna Chick Peas[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10011049[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]ABC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Dairyland Danish Cheddar Block Coloured[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10011051[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ABC[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Dairyland Danish Cheddar Block White[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10036240[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]XXX[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Sunbulah Lamb Kebabs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86"]112[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]10010050[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Luna[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl65, width: 345"]Luna Chick Peas 400 Gm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	F	G	H	I	J
1	Customer code	Product code	Brand	Product
2	111	10010040	Luna	Luna Green Peas		Cus	Luna	ABC	XXX
3	111	10010050	Luna	Luna Chick Peas		111	2	0	0
4	112	10011049	ABC	Dairyland Danish Cheddar Block Coloured		112	1	2	1
5	112	10011051	ABC	Dairyland Danish Cheddar Block White
6	112	10011051	ABC	Dairyland Danish Cheddar Block White
7	112	10036240	XXX	Sunbulah Lamb Kebabs
8	111	10010040	Luna	Luna Green Peas
9	112	10010050	Luna	Luna Chick Peas 400 Gm
Sheet1

In H3 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$9=""),IF($A$2:$A$9=$G3,IF($C$2:$C$9=H$2,MATCH($B$2:$B$9,$B$2:$B$9,0)))),ROW($A$2:$A$9)-ROW($A$2)+1),1))