View Poll Results: ...

Voters
0. This poll is closed
  • ...

    0 0%
  • ...

    0 0%
+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    HK
    MS-Off Ver
    excel 2010
    Posts
    11

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

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,078

    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))
    Happy with my advice? Click on the * reputation button below

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    HK
    MS-Off Ver
    excel 2010
    Posts
    11

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

    Quote Originally Posted by Crooza View Post
    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,707

    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.
    Attached Files Attached Files
    Glenn



  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,707

    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. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,715

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

    Quote Originally Posted by panadol857 View Post
    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
    Last edited by bebo021999; 01-12-2021 at 05:07 AM.

  7. #7
    Registered User
    Join Date
    07-16-2019
    Location
    HK
    MS-Off Ver
    excel 2010
    Posts
    11

    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. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,078

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to calculate student grade level based on DOB
    By ExcelHelps in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-06-2021, 07:49 PM
  2. [SOLVED] How to use the formula to calculate the student's results in column I?
    By rayhen in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-12-2020, 11:23 AM
  3. Normalize repeated measurement (calculate ratio) using formula
    By bluesky6688 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2016, 11:48 PM
  4. Normalize repeated measurement (calculate ratio) using formula
    By bluesky6688 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2016, 11:41 PM
  5. Replies: 9
    Last Post: 02-09-2016, 11:42 AM
  6. Calculate hours difference excl weekends
    By DubX in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2012, 09:21 AM
  7. Replies: 7
    Last Post: 08-12-2010, 03:52 PM

Bookmarks

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