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!!

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))

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.

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.

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.

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

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

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1