Count Unique values w

[MUSTAFA20]

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.

I appreciate your help.

 

[chullan88]

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

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

 

[Aladin Akyurek]

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]

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

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

Thank you for your reply. Just one more thing please,
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

 

[AliGW]

Try this:

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

 

[MUSTAFA20]

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.

 

[Aladin Akyurek]

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]

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

 

[Aladin Akyurek]

Thanks Alot

You are welcome.