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.
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).
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.
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.
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.