Sumif / countif

[Excelgreenhand]

I have a situation like this with two columns
colA colB
100011 AA
100012 AA
100013 AA
100014 AA
1000XX AA
200011 AA
200012 AA
200013 AA
200014 AA
2000XX AA
300011 AA
300012 AA
300013 AA
300014 AA
3000XX AA
100011 BB
100012 BB
100013 BB
100014 BB
1000XX BB
200011 BB
200012 BB
200013 BB
200014 BB
2000XX BB
300011 BB
300012 BB
300013 BB
300014 BB
3000XX BB

The result to be obtained is I would like to grab under "colA" is the first 4 digits, if duplicate (unique only) then ignore, therefore
under AA for 1000 (1 only), 2000 (1 only) and 3000 (1 only) and
under BB for 1000 (1 only), 2000 (1 only) and 3000 (1 only).
Should it be done with COUNTIF/SUMIF for U N I Q U E values !?.

The above is sales data with the "colA" being the product code and "colB" the product classification.
If a "colC" be involved with the salesman info, like
colA colB colC
100011 AA SalesA
100012 AA SalesA
100013 AA SalesA
100014 AA SalesB
1000XX AA SalesC
xx
x
xx

It would be possible for one more criteria, therefore to sum up under
SalesA has under classification "AA" has how many sets of products sold (1st four digits of 1000XX is regarded as one set of product)

Any ideas.
Thanks.

 

[CyrusTheVirus]

including salesman, what are you wanting your data to like in the end? show results.

 

[Canapone]

Hi

array entered

[TABLE="width: 328"]
<tbody>[TR]
[TD="width: 328"]=SUM(IF(FREQUENCY(IF(LEN(A$1:A$100),IFERROR(MATCH(LEFT($A$1:$A$100,4)&B1:$B100,LEFT(A$1:A$100,4)&"AA",0),""),""),IF(LEN(A$1:A$100),IFERROR(MATCH(LEFT(A$1:A$100,4)&B$1:B$100,LEFT(A$1:A$100,4)&"AA",0),""),""))>0,1))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 328"]
<tbody>[TR]
[TD="width: 328"]=SUM(IF(FREQUENCY(IF(LEN(A$1:A$100),IFERROR(MATCH(LEFT(A$1:A$100,4)&"AA"&"SalesA",LEFT(A$1:A$100,4)&B$1:B$100&C$1:C$100,0),""),""),IF(LEN(A$1:A$100),IFERROR(MATCH(LEFT(A$1:A$100,4)&"AA"&"SalesA",LEFT(A$1:A$100,4)&B$1:B$100&C$1:C$100,0),""),""))>0,1))[/TD]
[/TR]
</tbody>[/TABLE]

You can use external references for "AA" or "salesA"


https://dl.dropboxusercontent.com/u/106022761/mrfrequenza.xlsx


Regards

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​
1​	100011​	AA		AA	3​
2​	100012​	AA		BB	3​
3​	100013​	AA
4​	100014​	AA
5​	1000XX	AA
6​	200011​	AA
7​	200012​	AA
8​	200013​	AA
9​	200014​	AA
10​	2000XX	AA
11​	300011​	AA
12​	300012​	AA
13​	300013​	AA
14​	300014​	AA
15​	3000XX	AA
16​	100011​	BB
17​	100012​	BB
18​	100013​	BB
19​	100014​	BB
20​	1000XX	BB
21​	200011​	BB
22​	200012​	BB
23​	200013​	BB
24​	200014​	BB
25​	2000XX	BB
26​	300011​	BB
27​	300012​	BB
28​	300013​	BB
29​	300014​	BB
30​	3000XX	BB

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

=SUM(IF(FREQUENCY(IF($B$1:$B$30=$D1,IF(LEN($A$1:$A$30)>=4,MATCH(LEFT($A$1:$A$30,4),LEFT($A$1:$A$30,4),0))),ROW($A$1:$A$30)-ROW($A$1)+1),1))

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	100011​	AA	SalesA		SalesA	SalesB	SalesC
2​	100012​	AA	SalesA	AA	2​	1​	2​
3​	100013​	AA	SalesB	BB	1​	0​	2​
4​	100014​	AA	SalesB
5​	1000XX	AA	SalesC
6​	200011​	AA	SalesA
7​	200012​	AA	SalesC
8​	100011​	BB	SalesC
9​	100012​	BB	SalesA
10​	100013​	BB	SalesC
11​	100014​	BB	SalesC
12​	1000XX	BB	SalesA
13​	200011​	BB	SalesC

In E2 control+shift+enter, copy across, and down:

=SUM(IF(FREQUENCY(IF($B$1:$B$13=$D2,IF($C$1:$C$13=E$1,IF(LEN($A$1:$A$13)>=4,MATCH(LEFT($A$1:$A$13,4),LEFT($A$1:$A$13,4),0)))),ROW($A$1:$A$13)-ROW($A$1)+1),1))

 

[Excelgreenhand]

Thank you all guys.
I found the solution, light and lamp .....and much more. The future is bright to my future Excel issues.
Salute to ALL!!!