Countif Based Upon Text Length

Need a formula that will counta the cell range (A7:A88) and return the number of cells that contain less than 10 characters.

Example:

Cell A7 = General Info Intro
Cell A8 = 1
Cell A9 = 2
Cell A10 = 2a

Desired result would return 3

Thanks in advance for your expertise!

This is a complex criteria (even though it sounds simple).

There are 2 ways I know of to do this:
1. use multiple columns
2. use an array function

I will explain the multiple columns first.

In column B (or any empty column) put in the formula:

=LEN(A7)<10

And fill down. You will see a bunch of TRUE and FALSE.

Next, change the formula to (and fill down):

=(LEN(A7)<10)*1

Now you will see a bunch of 1 and 0.

The total you want is the sum of this column.


To do this in a single formula, you need an Array Formula. Enter this formula:

{=SUM(1*(LEN(A7:A88)<10))}

You DO NOT enter the {}. Instead, after typing the formula, rather than pressing Enter, you press Ctrl+Shift+Enter. Excel understands this to mean you want an Array Formula and it enters the {} for you. (Caution: you need to use this same key combination every time you edit the formula.)

I was toying with several array formulas and making it more complex than this. Thank you for your fresh set of eyes and expertise! Works great.

Originally Posted by tralls

Need a formula that will counta the cell range (A7:A88) and return the number of cells that contain less than 10 characters.

Example:

Cell A7 = General Info Intro
Cell A8 = 1
Cell A9 = 2
Cell A10 = 2a

Desired result would return 3

Thanks in advance for your expertise!

=SUMPRODUCT(--(LEN(A7:A88)<10),--(A7:A88<>""))