# Help finding 0 value and most value

#### Mike75

##### New Member
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>

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Mike75

##### New Member
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

##### Board Regular
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

##### Well-known Member
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

##### New Member
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.

Replies
8
Views
677
Replies
10
Views
469
Replies
12
Views
742
Replies
18
Views
744
Replies
15
Views
529

1,191,008
Messages
5,984,134
Members
439,872
Latest member
ExcelRM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back