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?

Customer code	Product code	Brand	Product
111	10010040 <tbody> </tbody>	Luna <tbody> </tbody>	Luna Green Peas <tbody> </tbody>
111 <tbody> </tbody>	10010050 <tbody> </tbody>	Luna <tbody> </tbody>	Luna Chick Peas <tbody> </tbody>
112 <tbody> </tbody>	10011049 <tbody> </tbody>	ABC <tbody> </tbody>	Dairyland Danish Cheddar Block Coloured <tbody> </tbody>
112 <tbody> </tbody>	10011051 <tbody> </tbody>	ABC	Dairyland Danish Cheddar Block White <tbody> </tbody>
112 <tbody> </tbody>	10036240 <tbody> </tbody>	XXX	Sunbulah Lamb Kebabs <tbody> </tbody>
112 <tbody> </tbody>	10010050 <tbody> </tbody>	Luna	Luna Chick Peas 400 Gm <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[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))