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