 | |
| Mysterious Excel Formula Syntax to Count Text Cells (as NEVER Seen Before) |
It's safe to say this Excel formula syntax has NEVER been seen before. My team and I, did an extensive search on this mysterious Excel formula syntax, but found no information about it. It's a super short formula in Excel to count cells with text and ignore blank cells, numbers and also formula cells that result in blank cells or empty strings.
This is one of a kind Excel formula hack. I received this special syntax for the Excel COUNTIF function from Jack, one of our community members. It works perfectly although I have no idea why. Because it looks like we are applying a wrong Excel syntax (angry eyes). If we were to write the formula "correctly" it would only exclude the truly blank cells. But cells that include formulas which result in blank cells would still be included in the count.
Alternatively, we can use the Excel COUNTIF function with the placeholders ? and This Excel count formula which uses wildcards, will also also just count non blank cells and ignore formulas that result in blank cells as well as ignore zeros and other numbers. In a situation when we want to count all cells that include either text or numbers we can use SUMPRODUCT in combination with the LEN function.
This way we can check if the length of the content in the cells is at least 1 character. You can replace SUMPRODUCT with SUM if you have the new Dynamic Array Excel - if you have legacy Excel and use SUM instead of SUMPRODUCT you need to press Control+shift+enter.
Related Topics:
Post a Comment