COUNTIF extension?

[Silvabod]

Data array is 720 cells (90*6) containing integers between 1 and 49, is up
to 90 user selections of 6 different numbers in each row.
COUNTIF (repeated 49 times) returns the count of each of the 49 numbers,
into a report - no problems.

I'd now like to "cream off" the top 2 "counts", report the actual numbers,
and the number of times selected
Also, "cream off" the lowest counts, ditto.

there could be more than one number with an equal count, in both instances.

EXAMPLE the numbers "5" and "34" selected 17 times
the number "13" selected 15 times
(in practice, could be a dozen or so)
and The number " 35" selected 3 times
The number "17" selected 4 times
Is this possible, if so, how?

 

[Bob Phillips]

Use Large against the range of COUNTIFs, say

=LARGE(M1:M50,1)
and
=LARGE(M1:M50,2)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Silvabod]

Bob, thanks - halfway there! These give the highest and 2nd highest counts,
but I need the actual number (subject of the count) also reported.
The real count range is a 90*6 array.
For simplicity, let's say there's random numbers 1 - 5 in col A1:A10.
In cell C1 there's =countif(A1:A10,1), C2 is =countif(A1:A10,2) etc right
thru to C5 =countif(A1:A10,5).

Function (in E10) =LARGE(C1:C5,1) gives the highest count in range C1:C5,
but I need the SUBJECT of the count as well, in D10 ( i.e if the number 3
occurred 5 times, I want the "subject" (3) as well as the count (5) - also,
to cover the situation where there's more than 1 number with equal count (in
this example, could be 3 numbers all with 3 count).

Verbosely - my report, currently manual, covering 49 numbers, reads -
C (col)
|Most popular choices|
C D E F
|No | 5 | 17 | punters chose it
|No | 34 | 15 | punters chose it
(the uprights " | " represent cells)
I want the nos "5" and "34" reported, the subject numbers of the "=large"
formulae.

Possible?

 

[Bob Phillips]

Function (in E10) =LARGE(C1:C5,1) gives the highest count in range C1:C5,
but I need the SUBJECT of the count as well, in D10 ( i.e if the number 3
occurred 5 times, I want the "subject" (3) as well as the count (5) - also,
to cover the situation where there's more than 1 number with equal count (in
this example, could be 3 numbers all with 3 count).

LARGE(C1:C5,1) doesn't give the occurrence of the highest number, but the
highest number itself, i.e. the subject.

 

[Silvabod]

OK, Bob, thanks.
Am using the "=large" formula on an array of "countif" cells (which count
the occurrences of a specific number in another array), and it works.
But, I need the the number (subject) being counted in the =large formula.
Possible?

 

[Bob Phillips]

You could use this in E1 and copy down

=ROW()&" occurs "&LARGE($C$1:$C$5,ROW())&" times"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Silvabod]

Thanks Bob! have to go out - will try this later, post back.