Hi, I use this formula to get the top 5 most frequent words appear in a range with multiple rows and columns A1:F8:
For top 5 try this:
In K1 text value "instances" and then in K2 this array formula
=LARGE(IF(A$1:F$8<>"",COUNTIF(A$1:F$8,A$1:F$8)),SUM(K$1:K1)+1)
in J1 text value "text" and this array formula in J2
=INDIRECT(TEXT(MIN(IF(A$1:F$8<>"",IF(COUNTIF(A$1:F$8,A$1:F$8)=K2,IF(COUNTIF(J$1:J1,A$1:F$8)=0,ROW(A$1:F$8)*1000+COLUMN(A$1:F$8))))),"R0C000"),FALSE)
confirm both formulas with CTRL+SHIFT+ENTER and copy down as far as required
blanks will be ignored
It works, but when there are 2 words with the same frequency, it will not display the other. For example: "Love" occurs 4x, "Joy" occurs 4x and "Peace" occurs 3x, this formula will reveal Love as the topmost, then Peace as the second most frequent. How to get the actual order: 1st Love, 2nd Joy, 3rd Peace?
For top 5 try this:
In K1 text value "instances" and then in K2 this array formula
=LARGE(IF(A$1:F$8<>"",COUNTIF(A$1:F$8,A$1:F$8)),SUM(K$1:K1)+1)
in J1 text value "text" and this array formula in J2
=INDIRECT(TEXT(MIN(IF(A$1:F$8<>"",IF(COUNTIF(A$1:F$8,A$1:F$8)=K2,IF(COUNTIF(J$1:J1,A$1:F$8)=0,ROW(A$1:F$8)*1000+COLUMN(A$1:F$8))))),"R0C000"),FALSE)
confirm both formulas with CTRL+SHIFT+ENTER and copy down as far as required
blanks will be ignored
It works, but when there are 2 words with the same frequency, it will not display the other. For example: "Love" occurs 4x, "Joy" occurs 4x and "Peace" occurs 3x, this formula will reveal Love as the topmost, then Peace as the second most frequent. How to get the actual order: 1st Love, 2nd Joy, 3rd Peace?