# Sumif / countif

#### Excelgreenhand

##### Board Regular
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### CyrusTheVirus

##### Well-known Member
including salesman, what are you wanting your data to like in the end? show results.

#### Canapone

##### Active Member
Hi

array entered

Code:
``````[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]``````

Code:
``````[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

Last edited:

##### MrExcel MVP
 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

##### Board Regular
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!!!

Replies
2
Views
309

1,190,782
Messages
5,982,886
Members
439,803
Latest member
sushilneupane

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back