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:D13The 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:D13The 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?
=COUNTIF(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)>0This 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
