If you are staring at a mixed spreadsheet and wondering how do you count text in excel, you are asking a very practical question. A column may contain names, product labels, comments, dates, numbers, blank cells, and error values all at once, and a simple total of the cells will not tell you how many of them actually hold words. In this guide, you will learn how do you count text in excel using COUNTA, SUMPRODUCT, and filtering approaches that are distinct from a single-criteria COUNTIF count. These methods are useful when you want to know how much text is present, how many entries are filled in, or how many cells match a text pattern without counting numbers by mistake. By the end, you will be able to choose the right formula for a quick total, a filtered view, or a more careful check that separates real words from blanks, spaces, and numbers stored as text.
The extra words sometimes attached to this search phrase, such as a cosy-sounding suffix in a search suggestion, are not a feature of Excel and do not change the formulas below. This is a plain written guide to spreadsheet counting, and every method here works in the normal desktop and web versions of Excel with ordinary cell ranges. Before you start, it helps to know what kind of text you are counting. Are you counting every non-empty cell, only cells that contain letters, only cells that contain a particular word, or only the visible rows after a filter? Each question has a slightly different answer, and picking the wrong one is the most common reason a count looks too high or too low. The examples in this guide use a small sample range, A2:A20, so you can adapt the formulas by changing that range to match your own data.
How Do You Count Text in Excel With COUNTA?
The fastest answer to how do you count text in excel, when you simply want to know how many cells are not empty, is COUNTA. The COUNTA function counts every cell in a range that is not empty, including cells that contain text, numbers, dates, logical values, and error values. It does not count truly blank cells. So if your range contains only words and blanks, COUNTA gives you exactly the text count you need. If your range also contains numbers, COUNTA will count those too, which means you need to interpret the result carefully or move to a more selective method. The basic formula looks like this: COUNTA with the range A2:A20 inside the brackets. Type it into an empty cell, press Enter, and Excel returns the number of filled cells in that range.
COUNTA is especially helpful for checking data entry. Suppose you have a list of twenty customer feedback comments and you want to know how many rows were actually filled in. COUNTA tells you at a glance whether five rows are still blank, without you having to scroll and count by eye. It is also useful for attendance lists, survey responses, task names, and any list where a filled cell means an entry exists. One important detail is that a cell containing only a space, or a formula that returns an empty-looking text string, is not truly blank to Excel, so COUNTA will count it. That is not a fault in the function; it is a reminder that invisible characters still count as content. If your count is higher than expected, check for stray spaces, formulas that return blank text, or cells that were cleared with the Delete key in a way that left formatting but no value.
Another strength of COUNTA is its simplicity for whole columns and tables. You can count a whole column, a table column, or several ranges at once by listing them separated by commas. For a table, COUNTA can count the entries in a named column without you having to remember the exact row numbers. Keep in mind, though, that counting a whole column will also count the header row if you include it, so start your range at the first data row rather than the header. For most everyday checks, COUNTA is the right first step, and the more advanced methods below are for cases where COUNTA alone is too broad.
When COUNTA Is Enough
COUNTA is enough when every filled cell in your range is meant to be counted. That includes a pure list of names, a column of product descriptions, or a set of open-ended survey answers with no numbers mixed in. In those cases, the number COUNTA returns is the text count, and there is no need for a more complex formula. It is also enough for a quick completeness check, such as confirming that all twenty rows of a form have been filled before you move on to analysis. The key test is simple: if a number in that range should not be counted as text, COUNTA is not precise enough on its own.
When COUNTA Overcounts
COUNTA overcounts whenever numbers, dates, or error values sit in the same range as your text. A date is stored as a number in Excel, so a column that mixes dates with comments will have its dates counted by COUNTA. The same is true for quantities, prices, and percentages. Error values, such as a formula that could not calculate, are also counted as non-empty. If you need a count of text only, and your range mixes types, use the SUMPRODUCT approaches in the next sections instead. They test each cell for text rather than simply checking whether it is empty.
How Do You Count Text in Excel With SUMPRODUCT?
SUMPRODUCT gives you a much more precise answer to how do you count text in excel when numbers are mixed in with words. The idea is to test every cell in the range and turn the test into a one or a zero, then add up the ones. The ISTEXT function is the test at the heart of this method. ISTEXT returns TRUE when a cell contains text and FALSE otherwise, and SUMPRODUCT converts those results into a count. The formula pairs SUMPRODUCT with a double negative and ISTEXT applied to the range, so each text cell contributes one to the total and each number, blank, or error contributes zero. This is a powerful pattern because it counts only genuine text, not numbers, dates, or blanks.
To use this method, select an empty cell and enter a SUMPRODUCT formula that tests your range with ISTEXT. For the sample range A2:A20, the formula checks each of those nineteen cells in turn. A cell holding the word Approved adds one. A cell holding the number 42 adds zero. A truly blank cell adds zero. The result is the number of cells that contain text and nothing else in the sense that matters here: text values as Excel understands them. This method is particularly valuable for messy imported data, where a column may contain a mix of codes, quantities, and notes. It lets you answer the question of how much real text is present without first cleaning the whole column.
SUMPRODUCT can also be adapted to count text that meets an extra condition. For example, you can count text cells that are longer than a certain length, text cells that contain a particular word, or text cells in rows where another column has a certain value. Each extra condition is added as another test inside the same SUMPRODUCT structure, so the formula stays in one cell and updates automatically when the data changes. That flexibility is why many experienced spreadsheet users prefer SUMPRODUCT for text counting in mixed data, even though COUNTA is quicker for a simple filled-cell check. The trade-off is readability: a SUMPRODUCT formula looks more complex at first glance, so add a short label next to it explaining what it counts, such as Text entries only.
Counting Text That Contains a Particular Word
Sometimes you do not want every text cell, only those that mention a word such as Pending, Returned, or Urgent. SUMPRODUCT combined with a search test can do this. The search test looks inside each cell for the word you specify and returns a position number when it finds it, or an error when it does not. Wrapping that test in a check for a number turns the result into a one or zero, and SUMPRODUCT adds up the matches. This approach is case-insensitive in the usual search sense, so Pending and pending are treated the same way. If you need a case-sensitive match, a different search function can be used instead, but for most lists the ordinary search approach is sufficient and easier to maintain.
Counting Text Longer Than a Few Characters
Another useful variation is counting only text entries that are longer than a short code or placeholder. A single letter, a dash, or a two-character code may technically be text, but it may not be a meaningful entry for your purpose. Combining SUMPRODUCT with a length test lets you count only text cells whose length exceeds a threshold you choose, such as more than three characters. This is helpful for survey answers, where a one-letter response may be a placeholder, or for comment fields, where you want to know how many people wrote a substantial note rather than a tick mark. As with the other SUMPRODUCT methods, the threshold is part of the formula, so you can adjust it without restructuring the spreadsheet.
Counting Text in Filtered and Visible Rows Only
Filtering changes the question. When you filter a list, you usually want to count only the rows you can see, not the rows hidden by the filter. COUNTA and the SUMPRODUCT methods above count hidden rows as well, because they look at the whole range regardless of visibility. To count only visible text, you need a method that respects the filter. One approach is to use SUBTOTAL with the COUNTA option, which counts only visible cells in a filtered range. SUBTOTAL is designed for exactly this situation: it ignores rows hidden by a filter while still counting the visible filled cells. This makes it ideal for a filtered sales list, a filtered task list, or any report where the reader has narrowed the data first.
The SUBTOTAL approach has its own limits. It counts visible non-empty cells, so like COUNTA it will count visible numbers as well as visible text. If your filtered range mixes numbers and text, you may need to combine the visible-row idea with a helper column. A helper column can mark each row with a simple test, such as whether the cell contains text, and then SUBTOTAL can count only the visible marks. The helper column does not need to be shown in a printed report; it can sit in a far-right column that you hide before sharing. This two-step method is more work to set up, but it gives an accurate visible-text count in mixed data, which neither COUNTA nor SUBTOTAL alone can guarantee.
A simpler manual alternative is the status bar. When you select a range in Excel, the status bar at the bottom of the window shows a count of the selected cells that are not empty, along with other summaries. This is a quick visual check rather than a formula, and it does not update a report automatically, but it is perfect for a one-off question. If you filter first and then select the visible cells, the status bar reflects the selection you made. For a formal report or a dashboard that must stay current, use a formula instead of relying on a manual selection.
Cleaning Data Before You Count
Many text counts go wrong not because the formula is wrong, but because the data contains hidden problems. Leading and trailing spaces, non-breaking spaces from a web import, and cells that look blank but contain a formula returning empty text are the usual culprits. Before you trust a count, scan a sample of the data. Turn on a view that shows spaces, or use a length check on a few cells to see whether a supposedly empty cell actually has a length greater than zero. If you find stray spaces, the TRIM function can remove extra spaces from text, and a careful find-and-replace can remove non-breaking spaces. Cleaning first, then counting, gives a result you can defend.
Numbers stored as text are another common trap. A code such as 00123, or a year typed with a leading apostrophe, may look like a number but is stored as text, so ISTEXT will count it as text. Whether that is correct depends on your question. If you are counting product codes, that is exactly what you want. If you are counting words, a numeric code should not be counted, and you will need an extra test to exclude cells that look like numbers. The distinction matters, and it is worth stating in your report: say whether codes count as text for your purpose, so a reader does not misread the total.
For a deeper look at counting cells with text in a related spreadsheet app, see Google Sheets counting cells with text. The ideas transfer well, because the question of what counts as text is the same even when the app changes. You may also find how emoji are counted as words useful if your text includes symbols, since symbols and emoji can affect word counts in surprising ways. Keeping those edge cases in mind will make your Excel counts more reliable.
Which Method Should You Use?
The table below summarises the main methods, what each one counts, and when to choose it. Use it as a quick reference when you open a new spreadsheet and need to decide in seconds rather than testing every formula.
| Method | What it counts | Best for | Watch out for |
|---|---|---|---|
| COUNTA | All non-empty cells | Quick filled-cell totals in text-only lists | Counts numbers, dates, and errors too |
| SUMPRODUCT with ISTEXT | Cells containing text only | Mixed ranges with numbers and words | Looks complex; label the result clearly |
| SUMPRODUCT with search | Text containing a chosen word | Counting mentions of a status or keyword | Partial matches count unless you anchor the test |
| SUBTOTAL | Visible non-empty cells | Filtered lists | Counts visible numbers as well as text |
| Status bar selection | Selected non-empty cells | One-off visual checks | Not a formula; does not update a report |
A Worked Example You Can Copy
Imagine a column headed Feedback, with nineteen rows beneath it. Ten rows contain written comments, four rows contain numbers from a rating import, three rows are blank, one row contains a date, and one row contains a single dash as a placeholder. COUNTA on that range returns sixteen, because it counts every filled cell. The SUMPRODUCT with ISTEXT method returns eleven, because it counts the ten comments plus the dash, which is technically text. A SUMPRODUCT method that also requires a length greater than one character returns ten, which is the count of substantial comments. All three answers are correct for different questions, which is why the question you ask matters more than the formula you pick. State the question in plain words next to your result, such as Substantial text comments, so anyone reading the sheet knows what the number means.
Try the same example with a filter applied. Filter the list to show only rows from a particular month, and SUBTOTAL will count only the visible filled cells, while COUNTA and SUMPRODUCT will still count the whole range. That difference is not an error; it reflects the different jobs of the functions. If your report is meant to describe the filtered view, use SUBTOTAL or a helper column. If your report is meant to describe the whole dataset, use COUNTA or SUMPRODUCT. Mixing the two without labelling them is how a dashboard ends up with two different totals for what looks like the same thing.
Frequently Asked Questions
What is the difference between COUNTA and COUNT?
COUNT counts only cells containing numbers, while COUNTA counts all non-empty cells, including text, numbers, dates, and errors. If you want a text count in a text-only list, COUNTA is the closer match. If you want numbers only, use COUNT. Neither function on its own counts text only in a mixed range, which is why the SUMPRODUCT with ISTEXT method exists.
Why does my count include cells that look blank?
A cell can look blank while still containing a space, a formula that returns empty text, or formatting that makes it appear filled. COUNTA counts such cells as non-empty. Check the length of a sample of those cells, remove stray spaces, and re-count. If a formula is returning empty text, consider whether that formula should return a true blank instead, or adjust your counting method to exclude short or empty-looking results.
Does counting text care about upper and lower case?
For a general text count with COUNTA or ISTEXT, case does not matter, because the test is only whether the cell contains text. Case matters only when you search for a particular word and want an exact case match. In that case, use a case-sensitive search test inside SUMPRODUCT, and test it on a small sample before applying it to the whole column.
How do I count only the text I can see after filtering?
Use SUBTOTAL with the COUNTA option for visible filled cells, or add a helper column that marks text cells and then use SUBTOTAL on that helper column for a visible text-only count. The status bar selection count is a quick manual alternative for a one-off check, but it will not update automatically if the filter changes.
Conclusion
Learning how do you count text in excel comes down to matching the method to the question. Use COUNTA for a fast filled-cell total in a text-only list. Use SUMPRODUCT with ISTEXT for a precise text-only count in mixed data. Use SUMPRODUCT with a search or length test when you need only certain words or substantial entries. Use SUBTOTAL, or a helper column with SUBTOTAL, when the count must respect a filter. Clean stray spaces and decide how numeric codes should be treated before you trust the total, and label every result in plain words so the number cannot be misread. With those habits, your text counts will be accurate, repeatable, and easy for anyone else to understand.
