Count unique value comparing multiple coloumn

[pratheesh1983]

Hello,

I have a table which has two columns, column A & B - Column A have "section" and B consists of User Name. In column B user column the entries will be repeating. I need a formula which will give me the count of unique value base on the section (ref. Table 2) eg. for Section A = no. of unique value is 4. Could any one please assist me to formulate formula for this?




Table 1

[TABLE="width: 204"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: left"]SECTION[/TD]
[TD="align: left"]USER[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]RAMESH T K[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]RAMESH T K[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]ARUN M[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]ARUN M[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]MATHEW W[/TD]
[/TR]
[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]MATHEW P[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]PAUL PK[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]PAUL PK[/TD]
[/TR]
[TR]
[TD="align: left"]B[/TD]
[TD="align: left"]TOM LG[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]TOM LG[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]ASHISH N[/TD]
[/TR]
[TR]
[TD="align: left"]C[/TD]
[TD="align: left"]PARTRICK P[/TD]
[/TR]
</tbody>[/TABLE]

Table 2

[TABLE="width: 237"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2, align: center"]UNIQUE VALUE[/TD]
[/TR]
[TR]
[TD]SECTION[/TD]
[TD]COUNT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

SECTION​

[/td][td]

USER​

[/td][td][/td][td]

UNIQUE VALUE​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

RAMESH T K​

[/td][td][/td][td]

SECTION​

[/td][td]

COUNT​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

A​

[/td][td]

RAMESH T K​

[/td][td][/td][td]

A​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

A​

[/td][td]

ARUN M​

[/td][td][/td][td]

B​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

A​

[/td][td]

ARUN M​

[/td][td][/td][td]

C​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

A​

[/td][td]

MATHEW W​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

A​

[/td][td]

MATHEW P​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

B​

[/td][td]

PAUL PK​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

B​

[/td][td]

PAUL PK​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

B​

[/td][td]

TOM LG​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

C​

[/td][td]

TOM LG​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

C​

[/td][td]

ASHISH N​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

C​

[/td][td]

PARTRICK P​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in E3 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$13=D3,MATCH(B$2:B$13,B$2:B$13,0)),ROW(B$2:B$13)-ROW(B$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[DanteAmor]

Another option with "regular" formula


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:98.85px;" /><col style="width:19.96px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SECTION</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">USER</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SECTION</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">UNIQUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A</td><td >RAMESH T K</td><td > </td><td style="text-align:center; ">A</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A</td><td >RAMESH T K</td><td > </td><td style="text-align:center; ">B</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A</td><td >ARUN M</td><td > </td><td style="text-align:center; ">C</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A</td><td >ARUN M</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A</td><td >MATHEW W</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A</td><td >MATHEW P</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >B</td><td >PAUL PK</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >B</td><td >PAUL PK</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >B</td><td >TOM LG</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >C</td><td >TOM LG</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >C</td><td >ASHISH N</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >C</td><td >PARTRICK P</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E2</td><td >=SUMPRODUCT(($A$2:$A$13=D2)*(MATCH($A$2:$A$13&$B$2:$B$13, $A$2:$A$13&$B$2:$B$13,0)=ROW($B$2:$B$13)-1))</td></tr></table></td></tr></table>