How To Count Cells That Contain Exactly N Characters

Example

=COUNTIF($B$3:$B$11,REPT(“?”,D3))

Download Example File

Generic Formula

=COUNTIF(Range,REPT(“?”,N))

Range – This is the range of cells to count.
N – This the number of characters to count.

What It Does

This formula will count the number of cells in a given range which contain exactly N characters.

How It Works

This formula makes use of the question mark “?” character which is a wildcard for exactly one character.

REPT(“?”,N) will return a text string made of exactly N question marks. In our example REPT(“?”,2) returns “??“.

COUNTIF(Range,”??”) will then count all the cells in the Range that contain exactly 2 characters because of the two “?” wildcard characters in the COUNTIF criteria. In our example Range has 3 cells which contain exactly 2 characters, so COUNTIF(Range,”??”) returns 3.

About the Author

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.