Formula to Return Specific Ranges of Numbers in Order of Frequency

Hello all! I'm back again with a new conundrum.

I have a variety of numbers in cell range P2:S14. Some of these numbers show up more than once.

I would like cell U2 to return the most frequently occurring number, from 1-9, from the above-designated range. In cell U3, it would be the 2nd most frequently occurring number, from 1-9 in, that range, etc.

I need to continue this pattern in cell V2 for #'s 10-19, W2 for #'s 20-29, and so on and so forth.

So let's say the number 3 shows up 4 times in the P2:S14 range, and the number 7 shows up 3 times. Cell U2 would populate the number 3 while Cell U3 populates the number 7.

I hope I've painted this clearly enough. Thank you in advance for your help.

Hi runelady,

See the attached to see if this is the answer you are looking for.
Countif in Range.xlsx

Originally Posted by MarvinP

Hi runelady,

See the attached to see if this is the answer you are looking for.
Attachment 484910

It's close, but instead of returning the number of occurrences, it produces the number which appears most frequently, followed by the second most frequent in the 1-9, 10-19, 20-29, etc. range.

I have attached a screenshot. The formula does not exist where the #3 appears under the "1's" column. I typed that in because it's just obvious that 3 repeats more than any other single digit number.
Example.png

You have edited your post while I was away having a delicious steak.... So. I think this is the answer to your original Q. It may NOT be the answer to your current Q, 'cos I'm not sure what you want. so take a look and then explain IN A REPLY TO THIS THREAD (not by amending post 1 again, or starting a new thread).

I think I figured out the issue with the 2nd row. I can't get the 50's to populate though. I think there may not be multiples of the 50's, which is why it's not populating. I think this works.

Hi,

Here is another answer using an additional table.
Countif in Range try 2.xlsx

Here's another one.

This will only return multi-modal numbers (numbers that appear at least twice).

Data Range

	A	B	C	D	E	F	G	H	I
1	1	12	20	30		1-9	10-19	20-29	30-39
2	1	13	21	31		7	13	23	34
3	2	13	23	32		1	16	25
4	4	13	23	33		5		26
5	5	13	24	34
6	5	15	25	34
7	6	16	25	34
8	7	16	26	35
9	7	18	26	36
10	7	19	27	38
11	----	----	----	----	----	----	----	----	----

This array formula** entered in F2:

=IFERROR(MODE(IF(ISNA(MATCH($A$1:$D$10,F$1:F1,0)),IF($A$1:$D$10>=--SUBSTITUTE(LEFT(F$1,2),"-",""),IF($A$1:$D$10<=--SUBSTITUTE(RIGHT(F$1,2),"-",""),$A$1:$D$10)))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to I2 then down until you get a row full of blanks.

I have the numbers sorted just to make it easier to see what results you should expect.

You're welcome!