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

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Count the no. of occurrences of a no. greater or equal to the chosen no. from a l

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