## View Poll Results: ...

Voters
0. This poll is closed
• ...

0 0%
• ...

0 0%

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

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

How can I use formula to directly calculate the how many student no. (excluding the repeated no.) occurred in the table?

Thank you!!  Register To Reply

2. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.)

Try this

=SUM(1/COUNTIF(B7:B16,B7:B16))  Register To Reply

3. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.) Originally Posted by Crooza Try this

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

Dear Crooza

1. #DIV/0! is occurred in your provided formula =SUM(1/COUNTIF(B7:B16,B7:B16))

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 (

Thank you.  Register To Reply

4. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.)

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

5. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.)

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.  Register To Reply

6. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.) 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  Register To Reply

7. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.)

Noted and Sorry. The poll seems default during my issued the post. I will pay attention for this on next time. Thank you  Register To Reply

8. ## Re: How to use formula to directly calculate how many student no. (excl. the repeated no.)

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  Register To Reply