How can I use formula to directly calculate the how many student no. (excluding the repeated no.) occurred in the table?
Thank you!!
...
...
How can I use formula to directly calculate the how many student no. (excluding the repeated no.) occurred in the table?
Thank you!!
Try this
=SUM(1/COUNTIF(B7:B16,B7:B16))
Happy with my advice? Click on the * reputation button below
You must have managed to make a mistake copy/pasting the formula. It works just fine.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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.
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
Last edited by bebo021999; 01-12-2021 at 05:07 AM.
Quang PT
Noted and Sorry. The poll seems default during my issued the post. I will pay attention for this on next time. Thank you
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)
Bookmarks