How to use formula to directly calculate how many student no. (excl. the repeated no.)?

Try this

=SUM(1/COUNTIF(B7:B16,B7:B16))

You must have managed to make a mistake copy/pasting the formula. It works just fine.

panadol857. I have no idea why you attached a poll to your thread. It is totally irrelevant. Do not do so again. Unfortunately I can not see how to delete it.

Originally Posted by panadol857

2. And I just try & try use =SUMPRODUCT(1/COUNTIF(B7:B16,B7:B16)), the outcome is "7". Is it all right?? But actually I not clear the principle of this formula (

Same outcome.
SUM must be confirmed by combination of Ctrl-Shift then Enter
SUMPRODUCT needs Enter only.

How it works:

COUNTIF(B7:B16,B7:B16) establish list of COUNT of single cell in range, like COUNTIF(B7:B16,B7) then COUNTIF(B7:B16,B8)...

become: {2;2;2;1;2;1;1;1;2;2} (count of B7 is 2, count of B8 is 2,...,B16 is 2)

Now we need to change 2 to 1/2, then sum up each pair of 2 to be 1 . It mean B7 and B8 will be count 1.

If we have 3 duplicate values, we will have three of 3, then 1/3+1/3+1/3 =1

So, 1/COUNTIF(B7:B16,B7:B16) should be {0.5;0.5;0.5;1;0.5;1;1;1;0.5;0.5}, then sum = 7

Sorry forgot to mention it is an array formula but as pointed out above changing Sum to Sumproduct addresses this. I see bebo explained how it works. As he/she said the inverse of each count means that the sum of each inverse group always adds up to 1 so each group of like entries (regardless of how many there are) will always add to one. Neat trick hey