Counting individual numbers in a range of cells with multiple numbers per cell

[themeekclan]

I'm looking for help to figure out how to count how many times a number occurs in a range of cells where there are multiple numbers per cell.

For example, if the range I'm searching has 351, 123, 240, 95, 28 and 111 located in multiple cells (not in any single column or row), how do I count how many one's, two's, three's, etc there are.

 

[oldbrewer]

using helpers A1=351 B1=3 C1=5 D1=1 then have 10 more columns counting the 1's, 2's etc in E1 to N1

 

[Marcelo Branco]

Welcome to Mr Excel

You can try something like this

	A​	B​	C​	D​	E​	F​	G​
1​					Concatenation​	Number​	Count​
2​	351​				3512402812395111​	1​	5​
3​			123​			2​	3​
4​	240​					3​	2​
5​			95​			4​	1​
6​	28​					5​	2​
7​			111​			6​	0​
8​						7​	0​
9​						8​	1​
10​						9​	1​

Formula in E2
=A2&A4&A6&C3&C5&C7

Formula in G2 copied down
=LEN($E$2)-LEN(SUBSTITUTE($E$2,F2,""))

Hope this helps

M.

 

[themeekclan]

Thanks, that really helped! I got it to work.