Forum Discussion
formula for list top ten occurring numbers in a complete spreadsheet
Hi all, i am trying to find a formula for finding the top 10 most occurring number from B5 to U17
2 Replies
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.
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.
