To exclude "false positive" blank cells from the count, use the COUNTIFS function with the "excluded" character in the second criterion.įor example, to count cells with text in the range A2:A7 ignoring those that contain a space character, use this formula: As a result, a visually blank cell gets counted by the formula causing a user to pull out their hair trying to figure out why :) In some situations, however, that might be confusing because certain cells may only look empty but, in fact, contain characters invisible to the human eye such as empty strings, apostrophes, spaces, line breaks, etc. The formulas discussed above count all cells that have any text characters in them.
How to count number of cells with text in Excel
All the formulas work in Excel 2019, 2016, 20. (Note that COUNTIF(BM2:BQ2,"") has the same issue as COUNTA.How do I count cells with text in Excel? There are a few different formulas to count cells that contain any text, specific characters or only filtered cells.
Based on the fact that you used the wording "I want to return another blank", it would appear that you're defining "blank" as cells that appear blank, but which may actually contain a formula that outputs "" and therefore aren't empty. An important thing to consider is your definition of "blank".