Excel’s Secret Count Text Cells Trick

It’s not beyond the realm of possibility that we at XelPlus are asked an Excel question that we can’t answer.  In those cases, a quick (your favorite search engine name here) search will usually provide the answer.  If not, our vast network of MVPs and gurus will conjure up the solution.

In this case, we’re stumped.

This is a post that serves two purposes:

  1. to showcase a unique and cleaver syntax “feature” in Excel, and
  2. ask for someone to solve the mystery of how it works.

Will there be a prize for the winner?  If you count knowing something that some of the greatest Excel minds didn’t know, then yes.  Bragging rights always feel good.

If you feel you’re up for the challenge, continue reading.  We look forward to your responses and hopefully an answer to the mystery.

The Problem

Our task at hand is to count the number of cells that contain text in an output column.

There are some additional parameters:

  • Exclude any cells that contain numbers
  • Exclude blank cells
  • Exclude any cells that contain empty strings as the result of formulas

In our example, we can manually determine that there are 5 cells that meet our criteria.

The cells we wish to ignore, blank cells or empty strings cells, are shown below.

Understanding What Excel Considers “Blank”

To be clear, an empty string is not the same as a blank cell.  A blank cell has nothing in it.  An empty string is the result of a formula that displays nothing as its result.  This is typically achieved by using two double-quote marks with nothing between them.

=IF(B4=”Game”, C4, “”)

The two double-quotes are known as “empty text” and display nothing but they count as data.

If we were to test a cell containing empty text with an ISBLANK function, we see that the ISBLANK function returns a result of FALSE, meaning that Excel doesn’t consider empty text the same as a truly blank cell.

The ISBLANK function returns a TRUE when pointing to a cell that contains nothing.

Enter the Mystery Formula

One of our community members, we’ll call him “Jack” (that’s actually his name), needed to count all of the text-only cells while ignoring the empty text cells.

Jack came across the following formula but didn’t understand how it worked.  He asked us at XelPlus how it worked, we were all clueless.

We reached out to our good friend Bob Umlas (you might remember him from previous hit posts) to see if he had seen this… he hadn’t.

If the legendary Bob Umlas hasn’t seen it before, then it is truly rare.

Begin with COUNTIF

We start the formula using a COUNTIF function because we want to count all cells in a range that meet the given criteria.

=COUNTIF(

Our range of cells to examine are cells D4 through D13.

=COUNTIF(D4:D13

The criteria for the COUNTIF will use the “not equal” operator “<>”.  But in this case, we will reverse the order of the greater-than/less-than symbols and write it as “><”.  We’ll call these “angry eyes”.

The finished formula is as follows:

=COUNTIF(D4:D13, “><”)

It works!!! It only counts the cells containing text while ignoring blank and empty string cells.

How does it work?  That’s the mystery.  If you know how this works and can provide an explanation, please post it in the comments below.  It’s time to claim those bragging rights.

Using Wildcards to Try to Solve the Puzzle

In an attempt to replicate the “angry eyes” logic using traditional Excel functions, we will try using a COUNTIF function in tandem with wildcards.

Wildcard Refresher

  • The asterisk (*) character is used to represent any character in any quantity
  • The question mark (?) character is used to represent any character but only a single character position

Our Formula

We start with the original COUNTIF and range…

=COUNTIF(D4:D13

The criteria will be to require at least 1 character to exist but can then have any number of characters following.  We will use a single “?” to denote the mandatory “at least 1 character” followed by the “*” to allow for as many or as few following characters.

=COUNTIF(D4:D13, “?*”)

This makes sense to us…

“?*”

…however, this does not.

“><”

Trying to Figure It Out

Since we understand the “<>” operator, what would the COUNTIF function return if we used it in the formula?

=COUTNIF(D4:D13, “<>”)

We are returned an answer of 8

This is counting every cell, including the empty string cells.  The only cells it ignores are truly blank cells.

Counting the “Blank” Cells

Is there a function in Excel that allows us to count the non-blank cells while ignoring the empty cells or the blank cells that are the result of a formula?

Functions that come to mind are the LEN and SUMPRODUCT functions.

We can create a formula that counts the length of the data in a cell to see if it is at least 1 character in length.  This would indicate the presence of information.

=LEN(D4:D13)>0

This returns an array of TRUE/FALSE responses.

We can convert these TRUE/FALSE responses by prefacing the function with a double-unary operator (2 minus signs).

=--(LEN(D4:D13)>0)

NOTE: The double-unary operator is an interesting little party trick Excel power-users use to force Excel to convert TRUE/FALSE responses to 1/0 responses.

Next, we’ll add all the values returned by the LEN function using the SUMPRODUCT function.

=SUMPRODUCT(--(LEN(D4:D13)>0))

ANOTHER NOTE:  If you are using the latest version of Office 365 and have received the Dynamic Array calculation engine, you could perform this operation using a SUM function instead of a SUMPRODUCT function.

=SUM(--(LEN(D4:D13)>0))

Although this is shorter, it fails in previous versions of Excel because the SUM function can’t handle arrays; it returns a #Value! error message.  You can enter the formula using CSE notation (CTRL-Shift-Enter) to “upgrade” the SUM function to an array function capable of dealing with array responses.  We’re using the SUMPRODUCT function because it can handle array responses natively without the need for special finger acrobatics.

Not a True Replacement

Although the above formula counts cells with text while ignoring blank or empty text cells, it doesn’t ignore cells containing numbers.

Without additional logic to screen out the numbers, the “angry eyes” formula is still the clear winner.

Thanks to Jack

Thank you Jack for bringing this hidden “hack” to our attention.  This will forever be known as the “Jack-Hack”.

If anyone has any theories as to how this works and what its thought process is, post your comments below the YouTube video HERE.  We’d all like to see this mystery solved.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials