Help finding 0 value and most value

[Mike75]

I have a range of B2:U18 in these cell I input a number

At the bottom in row 21 I have countif counting the different numbers

Now in row 24 I would like it to tell me what numbers have a counting value of 0

And in row 26 I would like it to tell me what numbers I have the most of

I had a formula for row 24 in another file but when I tried to rewrite for this file it didn't work

It got me thinking if it could tell me what I have the less of could it tell me what I have the most of

it would make my job easier and faster

	1		3	4	5	6		8	9	1			4	5	6		8	9	1
	10		11		12		13		14		15		16		17		18		19
	3	4	5	6		8	9	1				5	6		8	9	1
		18		17		16		15		14		13		12		11		10		20
	5			8	9	1							8	9	1
	19		20		19		18		17		16		15		14		13		12
		8	9	1								9	1
		18		17		16		15		14		13		12		11		10		11
			1		3	4	5	6		8	9	1			4	5	6		8	9
	19		20		19		18		17		16		15		14		13		12
	1				5	6		8	9	1					6		8	9	1
		20		19		18		17		16		15		14		13		12		11
						8	9	1								9	1
	19		18		17		16		15		14		13		12		11		10
						1		3	4	5	6		8	9	1			4	5	6
		20		19		18		17		16		15		14		13		12		11
		8	9	1				5	6		8	9	1				5	6		8
	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
	20	0	4	7	12	12	0	0	17	4	7	8	8	8	8	8	8	8	8	5
	Missing Items
	Most Items

<tbody>
</tbody>

 

[Mike75]

Going over my report I realize there is one other thing I need from this report. Numbers used/found in the report.

I used numbers 1 - 20 in the report above but numbers 2 and 7 where not used. So 18 numbers where used.

Is there a way to make a formula for that

 

[Anand Sharma]

you can use equation as below

=COUNTIF(B2:U21,"=0") &"( Zeros)" will give how many zeros used

=COUNTIF(B2:U21,MAX(B2:U21))&" ("&MAX(B2:U21)& "s)" will give how many and which max number is used

 

[tygrrboi]

To get all the values not used,

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>{=IFERROR(INDEX($A$19:$T$19,1,SMALL(IF($A$20:$T$20=0,COLUMN($A$20:$T$20)),ROW(1:1))),"")}
Array Formula input with CONTROL SHIFT ENTER not just enter. The curly braces {} will appear automatically. Do not put them in manually.

Then drag this down.
red = Range where your actual numbers are stored
pink = Range where the count for each number is stored.

To get the values that appear the highest amount of time,

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>
{=IFERROR(INDEX($A$19:$T$19,1,LARGE(IF($A$20:$T$20=MAX($A$20:$T$20),COLUMN($A$20:$T$20)),ROW(1:1))),"")}
same deal with the Array Formula and ranges.

To get # of used and unused numbers, use Anand's formulas.

 

[Mike75]

Thanks for the help but not what I need. While waiting for help I did some researched for the and answer and figured part of the answer out. All I need is my countif row and us{=SMALL(B48:J48,2)} or {=LARGE(B48:J48,2)} and do it for 1,2,3, and 4 I will get the amount of the smallest and largeset

but the trouble I am having now is when I {=INDEX($B$47:$J$47,MATCH(C56,$B$48:$J$48,0))}
when I looked for the small and large and there was more than 1 of the same value it just input both of them than went to the next
when I try to index it now it only gives me the first one no matter if there are one, two, or more of the same value
I need to know what numbers have the same value.