Dec 04 2021 10:19 AM
Hi all, i am trying to find a formula for finding the top 10 most occurring number from B5 to U17
Dec 04 2021 11:07 AM
@ideepdosanjhHi,
A key may be to get all the numbers in one column to easier find and count unique numbers.
FILTERXML("<col><row>" & TEXTJOIN("</row><row>",0,B5:U17) & "</row></col>","//row")
FILTERXML and TEXTJOIN can do that and then UNIQUE; COUNTIFS and SORTBY as attached.
Dec 04 2021 03:58 PM
@ideepdosanjh
If you have a list 'seq' of possible numbers that might arise in the 'data' array
= COUNTIFS(data, seq)
will provide a list of frequencies for each number within 'seq'. To list the numbers in decending order of frequency you could use
= SORTBY(seq, freq, -1)
and INDEX could restrict the number of values returned to 10.
