# Count Unique values w

#### MUSTAFA20

##### New Member
Hi every one , I have the following Table, what I need to do is to count the No. of uniqe cases considering both column, when Ref & Sub ref match it is a unique case. for the below example, (Ahmed 6 ) is repeated in three rows but should be counted as 1, (Mourad 11) is 1, (Saleem 5) is one, (Ahmed 7) is one therefore I want to get a total of 4.
 Reference Sub reference Ahmed 6 Mourad 11 Ahmed 6 Ahmed 6 Ahmed 7 Saleem 5 Saleem 5 Count of Unique cases ??

<tbody>
</tbody>

In the table I have there are other data and other formulas therefore data filter and remove duplicates would not work, I need to use a formula for this.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### chullan88

##### New Member
Assuming your data is in the range A1:B7, the formula in C1:

=SUMPRODUCT((1/COUNTIFS(A1:A7,A1:A7,B1:B7,B1:B7)))

##### MrExcel MVP
In B9 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A8=""),IF(ISNUMBER(B2:B8),MATCH(A2:A8&"|"&B2:B8,A2:A8&"|"&B2:B8,0))),ROW(A2:A8)-ROW(A2)+1),1))

Since the sub references are just numbers, the following would suit the objective as well...

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A8=""),IF(ISNUMBER(B2:B8),B2:B8)),B2:B8),1))

Note that the FREQUENCY formulas are faster for this job than the equivalent COUNTIF(S) formulas.

#### MUSTAFA20

##### New Member
Assuming your data is in the range A1:B7, the formula in C1:

=SUMPRODUCT((1/COUNTIFS(A1:A7,A1:A7,B1:B7,B1:B7)))
As you assumed, Data is from A1:B7 If we assumed there is a third column C1:C7 were C1,C2,C3 & C6 have (Mustafa), C4,C5, & C7 have (Amer).

how would I amend the formula to return unique values corresponding to each teacher.

 A B C 1 Student Class Teacher 2 Ahmed 6 Mustafa 3 Mourad 11 Mustafa 4 Ahmed 6 Mustafa 5 Ahmed 6 Amer 6 Ahmed 7 Amer 7 Saleem 5 Mustafa 8 Saleem 5 Amer 9 10 Teacher No. of unique cases 11 Amer ? 12 Mustafa ?

<tbody>
</tbody>
<table border="0" cellpadding="0" cellspacing="0" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" style="width: "><colgroup><col><col><col span="px"2""></colgroup><tbody></tbody></table>

Thanks for the help again

Last edited:

#### AliGW

##### Banned
Try this:

=SUMPRODUCT((1/COUNTIFS(A1:A7,A1:A7,B1:B7,B1:B7,C1:C7,C1:C7)))

#### MUSTAFA20

##### New Member
In B9 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A8=""),IF(ISNUMBER(B2:B8),MATCH(A2:A8&"|"&B2:B8,A2:A8&"|"&B2:B8,0))),ROW(A2:A8)-ROW(A2)+1),1))

Since the sub references are just numbers, the following would suit the objective as well...

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A8=""),IF(ISNUMBER(B2:B8),B2:B8)),B2:B8),1))

Note that the FREQUENCY formulas are faster for this job than the equivalent COUNTIF(S) formulas.

Thank you. Although this would take more time for me to comprehend, I am new to frequency functions.

To complete my case I added one more assumption that I missed to mention at first place. I have added the scenario in my last reply. I could not do it using the normal ways i know, like sumif or countif functions. .

Thanks again.

##### MrExcel MVP
Thank you. Although this would take more time for me to comprehend, I am new to frequency functions.

To complete my case I added one more assumption that I missed to mention at first place. I have added the scenario in my last reply. I could not do it using the normal ways i know, like sumif or countif functions. .

Thanks again.

 Student Class Teacher Ahmed 6 Mustafa Mourad 11 Mustafa Ahmed 6 Mustafa Ahmed 6 Amer Ahmed 7 Amer Saleem 5 Mustafa Saleem 5 Amer Teacher No. of unique cases Amer 3 Mustafa 3

<tbody>
</tbody>

In B11 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$8=""),IF(ISNUMBER(\$B\$2:\$B\$8),IF(\$C\$2:\$C\$8=\$A\$11,\$B\$2:\$B\$8))),\$B\$2:\$B\$8),1))

1. On FREQUENCY function for counting unique items...

http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949

2. On COUNTIF(S) function for counting unique items...

http://www.mrexcel.com/forum/excel-questions/16682-count-unique-entries.html
http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html#post342159

3. On performance...

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

#### MUSTAFA20

##### New Member
 Student Class Teacher Ahmed 6 Mustafa Mourad 11 Mustafa Ahmed 6 Mustafa Ahmed 6 Amer Ahmed 7 Amer Saleem 5 Mustafa Saleem 5 Amer Teacher No. of unique cases Amer 3 Mustafa 3

<tbody>
</tbody>

In B11 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$A\$2:\$A\$8=""),IF(ISNUMBER(\$B\$2:\$B\$8),IF(\$C\$2:\$C\$8=\$A\$11,\$B\$2:\$B\$8))),\$B\$2:\$B\$8),1))

1. On FREQUENCY function for counting unique items...

http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949

2. On COUNTIF(S) function for counting unique items...

http://www.mrexcel.com/forum/excel-questions/16682-count-unique-entries.html
http://www.mrexcel.com/forum/excel-questions/70835-count-distinct-function.html#post342159

3. On performance...

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

Thanks Alot

Replies
1
Views
629
Replies
5
Views
643
Replies
3
Views
1K
Replies
6
Views
662
Replies
1
Views
136

1,191,025
Messages
5,984,202
Members
439,877
Latest member
kellylet

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

### Which adblocker are you using?

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

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