+ Reply to Thread
Results 1 to 8 of 8

Count the no. of occurrences of a no. greater or equal to the chosen no. from a list

  1. #1
    Registered User
    Join Date
    05-11-2011
    Location
    Malta
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Count the no. of occurrences of a no. greater or equal to the chosen no. from a list

    What I want to do is the following:

    e.g. I have a statistics list:

    46
    12
    102
    1
    33
    34
    55
    20
    29
    34
    200
    45
    93
    12

    If I choose 46, I want to know how many times the number 46 appears in the list as an amount, meaning that the number 46 in this list appears 5 times: in 46 itself, in the 102 (102 is greater or equal to 46), in 55, 200 and 93.

    In another example if I choose 12, in this case it appears 12 times: in 12 itself, and in the 102, 33, 34, 55, 20, 29, 34, 200, 45, 93 and 12 (since they are all greater or equal to the chosen number 12 itself).

    A formula close to what I tried was: =COUNTIF(C4:C28,MIN(H4)) but I only got 1 result since H4 is 46.

    How can I exactly define it?

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    Hi and welcome to the board

    Try
    Please Login or Register  to view this content.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    try:

    =COUNTIF(C4:C28, ">="&H4)

  4. #4
    Registered User
    Join Date
    05-11-2011
    Location
    Malta
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    Thanks a mill arthurbr and zbor for the swift reply!

    It's working perfectly as a formula ... one last adjustment:

    I need to just drag down the formula so it copies for all the other list amounts, cause at the moment =COUNTIF(C4:C28, ">="&H4) is changing to =COUNTIF(C5:C29, ">="&H5) and then to =COUNTIF(C6:C30, ">="&H6)

    In this case the H4 is increasing by 1 each time, to cell H5, H6, which I'm happy with, however what I need is for the C4:C28 to remain constant without having to change it manually in each formula (since I will have much larger lists to anaylse).

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    You need to lock first range:

    =COUNTIF($C$4:$C$28, ">="&H4)

  6. #6
    Registered User
    Join Date
    05-11-2011
    Location
    Malta
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    Great stuff!!

    Thanks for the help, I can honestly this is an A+++ forum, will recommend it for sure!!

  7. #7
    Registered User
    Join Date
    05-11-2011
    Location
    Malta
    MS-Off Ver
    Excel 2007/2010
    Posts
    7

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    another fine tuning needed:

    if for e.g. i have the following statistics:

    46 23
    12 26
    102 31
    1 27
    33 25
    34 21
    55 33
    20 23
    29 27
    34 28

    and I stated before for example if I choose the number 33, then I know that the numbers greater or equal to 33 are the 46, 102, 33, 34, 55 and 34 and it appears 6 times.

    Is it possible to choose automatically their respective adjacent number such as 23 (for 46), 31 (for 102), 25 (for 33), 21 (for 34) and add them up as a total?

    As I would need to use this "total" in another formula.

  8. #8
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

    This being another topic, please start a new thread - Thx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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