## Identify missing numbers in a range

*Article last updated on February 24, 2018*

**Question:** How do I find missing numbers between 1-9 in a range?

1 3 4

5 6 7

8 8 3

**Answer:**

**Array formula in cell B8:**

If you have numbers between 5 and 32, change ROW($1:$9) to ROW($5:$32)

The formula won´t work if you have numbers above 1048576 (excel 2007). See this post:

Identify missing values in a column using excel formula

**How to create an array formula**

- Copy above array formula
- Select cell B8
- Click in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter

**Named ranges***
tbl*(B3:D5)

What is named ranges?

**Download excel example file.**

Missing numbers in a range.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**ROW(**reference**)** Returns the rownumber of a reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**SMALL(**array,k**)** Returns the k-th smallest row number in this data set.

**ROWS(**array**)** returns the number of rows in a reference or an array

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in two columns based on a numerical range

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Insert rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that […]

How to calculate missing months in a given date range

Question: I have dates in a list. I would like to know how to identify missing months in this list […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Find missing numbers in a range containing multiple columns

Question: I have numbers spanning over multiple columns. I want to know the smallest value and the largest value and […]

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

### 5 Responses to “Identify missing numbers in a range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" These alternate across 1800 colunms of data.

My question: how do I count the number of groupings of each? In other words, across those 1800 columns, how many arrays of value 1 and how many arrays of value 0 do I have?

Thanks.

Joe

Joe,

see this post:

Count the number of groupings of each value

thanks

Is there any difference to apply this formula in only one column (F2:F1093)? I´m trying to identify missing numbers in a list but I´m making something wrong.

Felipe,

Did you enter it as an array formula?