Invoice numbers, check numbers and batch IDs are usually supposed to run without a gap. Finding a missing one by scanning a list by eye does not scale past a few dozen rows. This recipe compares how many numbers SHOULD exist between the smallest and largest value in the range against how many numbers actually DO exist — if those two counts do not match, something is missing.
Invoice numbers 1001 through 1010 should be 10 numbers; if only 9 are present, the formula reports exactly 1 missing.
The example
A batch of invoice numbers that should run 1001 to 1010 consecutively — but 1006 was voided and never re-entered.
| A | B | |
|---|---|---|
| 1 | Invoice # | |
| 2 | 1001 | |
| 3 | 1002 | |
| 4 | 1003 | |
| 5 | 1004 | |
| 6 | 1005 | |
| 7 | 1007 | |
| 8 | 1008 | |
| 9 | 1009 | |
| 10 | 1010 | Gap: 1 missing |
The formula
The count of numbers that should exist, versus the count that do:
How it works
Two counts, compared:
MAX(A2:A10)-MIN(A2:A10)+1is how many consecutive whole numbers SHOULD exist between the smallest and largest value — 1010 minus 1001 plus 1 is 10.COUNT(A2:A10)is how many numbers actually ARE in the range — only 9, because 1006 was never re-entered after being voided.- The IF compares the two: 10 does not equal 9, so it reports a gap, and subtracting the actual count from the expected count (10-9) tells you exactly how many numbers are missing, even without identifying which ones.
To find WHICH number is missing, not just how many, use a helper column that checks each expected number in the sequence against COUNTIF(Range,ExpectedNumber)=0.
Try it: interactive demo
Enter the smallest number, largest number and how many numbers are actually present.
Variations
Find exactly which numbers are missing
Generate the full expected sequence in a helper column, then flag any expected number with a zero COUNTIF match against the actual data.
Check for duplicates as well as gaps
A duplicate number can mask a gap in the simple count-comparison version — add a check that COUNT equals COUNTA of unique values too.
Pitfalls & errors
This method assumes the numbers are meant to be perfectly consecutive integers with no intentional skips. A numbering scheme that deliberately skips certain ranges (reserved blocks, voided-and-retired numbers) will show false gaps — exclude known intentional gaps before applying the check.
A duplicate number can offset a missing number and make the counts match by coincidence, hiding both problems. Pair this check with a duplicate check (see Variations) for a complete audit, not this formula alone.
Run this check as part of a routine month-end close, not just when something already looks wrong — the whole value of a gap check is catching the missing invoice before a customer calls asking why they were never billed.
Practice workbook
Frequently asked questions
Does this work for check numbers and batch IDs, not just invoices?
What about sequences with a prefix, like INV-1001?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class