If you have ever asked how do you count text in excel, you have probably discovered that the most obvious formula does not do the job. COUNT counts numbers, not text, so it will return zero for a column full of words even when that column is full. COUNTIF, by contrast, counts cells that meet a condition you specify, and that condition can be a piece of text, a pattern, or a comparison. That makes COUNTIF the standard tool for counting text in Excel, and this guide walks through it step by step. It covers exact matches, partial matches with wildcards, counting with multiple conditions, handling blanks, and fixing the common reasons a count looks wrong. No prior formula knowledge is assumed beyond knowing how to type a formula into a cell and press Enter.
Start with a simple example to keep in mind throughout. Imagine a column of survey answers in cells A2 through A20, containing words such as Yes, No, and Maybe. You want to know how many people answered Yes. The formula is COUNTIF with two parts: the range to search, and the criteria to match. Written out, it looks like COUNTIF(A2:A20, Yes) with quotation marks around Yes, because text criteria in Excel formulas are normally written in quotes. Excel will then look through A2 to A20, compare each cell with Yes, and return the number of matches. That is the whole idea. Everything else in this guide is a variation on that pattern: changing the range, changing the criteria, or combining COUNTIF with other functions.
how do you count text in excel with COUNTIF
The first question to answer about how do you count text in excel is what kind of match you want. An exact match counts only cells that contain exactly the text you specified, with no extra characters. A partial match counts cells that contain the text somewhere inside a longer entry. A case-sensitive question is also worth settling early: COUNTIF is not case-sensitive, so Yes, YES, and yes all count as matches for the criteria Yes. That is helpful for survey data where capitalisation varies, but it can surprise users who expected case to matter. If case-sensitive counting is truly needed, a different approach using other functions is required, and that is covered later as an advanced note, not as the main method.
Exact matching is the simplest case, and it is also the easiest to test. Type a small list in a column, such as Apple, Banana, Apple, Cherry, Apple, and try counting Apple. The formula COUNTIF with the range and Apple in quotes should return 3. If it returns something else, check three things first: that the range covers all the cells you intended, that the criteria text is spelled exactly as it appears in the cells, and that there are no extra spaces before or after the words in the cells. Extra spaces are a common hidden cause of mismatch, because Apple with a trailing space looks identical to Apple on screen but is not identical to Excel. Trimming spaces, or using a wildcard approach, can help when data comes from another system.
Partial matching uses wildcards, which are special characters that stand for other characters. The asterisk stands for any sequence of characters, including no characters at all. The question mark stands for exactly one character. So counting with the criteria containing part of a word, with asterisks on both sides, will count any cell that contains that word anywhere inside it. For example, counting cells containing the word red anywhere in the entry will catch Red, Dark red, and Reddish, but it will also catch any other entry containing those letters in sequence, which is why partial matching needs care. The tilde character is used to search for a literal asterisk or question mark, by placing it directly before the character you want to treat literally. Wildcards turn COUNTIF from a simple counter into a flexible search tool, but they also introduce the risk of overcounting if the pattern is too broad.
how do you count text in excel: worked examples
Worked examples make the differences concrete. Suppose column B contains product categories: Shoes, Shoes, Hats, Shoes, Bags. Counting Shoes exactly returns 3. Counting with a wildcard pattern that includes the letters hoe anywhere will also return 3 in this list, because only Shoes contains that sequence, but in a larger list it might also match other words containing the same letters. Suppose column C contains status labels: Complete, In Progress, Complete, Pending, Complete. Counting Complete returns 3, while counting with a pattern for Complete with an asterisk at the end would also return 3 here, but would additionally match an entry such as Completed if one existed. The lesson is to start with exact matching, and only switch to wildcards when you genuinely need to match part of a longer entry.
Another common task is counting cells that contain any text at all, regardless of what the text is. For that, COUNTIF can use a wildcard pattern consisting of a single asterisk, which matches any cell containing at least one character, but it does not count truly empty cells. A related function, COUNTA, also counts non-empty cells, including cells containing numbers, text, or errors, so the two approaches can give different answers when a range mixes numbers and text. If your goal is specifically to count text cells, and the range also contains numbers, a formula using SUMPRODUCT with ISTEXT is more precise, although it is more advanced. For most everyday lists that contain only text and blanks, COUNTIF with an asterisk pattern and COUNTA will agree, and either is fine.
Counting text that meets a second condition is where COUNTIFS, with an S, comes in. COUNTIFS allows multiple criteria across multiple ranges, and it counts only rows where all conditions are true. For example, you might count survey answers where column A is Yes and column B is North. The formula lists the first range and criteria, then the second range and criteria, and Excel counts only rows matching both. This is the natural next step after COUNTIF, and it avoids the error of counting two separate lists and assuming the overlap. It is also the basis for many summary tables, where each cell in the table counts rows matching a combination of row and column labels.
Blanks, errors, spaces, and case sensitivity
Blank cells cause more COUNTIF confusion than almost any other topic, because blank can mean several different things in Excel. A truly empty cell is blank. A cell containing a formula that returns an empty string looks blank on screen but is not truly empty, and it may be counted by some formulas and not by others. A cell containing a single space looks blank but contains a character, so it counts as text. If your count of blank cells looks wrong, the first step is to check which kind of blank you have. COUNTIF with empty quotes as criteria counts truly empty cells in some contexts, while COUNTBLANK counts both truly empty cells and formula-empty cells. Knowing which behaviour you need will save a lot of trial and error.
Spaces deserve their own warning. Data pasted from websites, forms, or other systems often contains leading or trailing spaces, non-breaking spaces, or double spaces inside entries. Those spaces make exact matching fail, even when the visible text looks correct. The TRIM function removes leading and trailing spaces and reduces multiple spaces inside text to single spaces, and it is often used in a helper column to clean data before counting. CLEAN removes certain non-printing characters. Using a helper column for cleaned text, and then counting that column, is a robust approach when data quality is uncertain. It also makes the workbook easier to audit, because the cleaned values are visible rather than hidden inside a complex formula.
Errors in a range can also affect counts in surprising ways. COUNTIF itself generally ignores error values unless the criteria specifically matches them, but a summary that also uses other functions may break if errors are present. If a range contains error values, it is usually better to fix the source of the error, or to handle it explicitly, rather than to build a counting formula that works around it silently. Case sensitivity is a separate issue. As noted, COUNTIF ignores case. For a case-sensitive count, a common approach combines SUMPRODUCT with EXACT, which compares text exactly including case. That formula is longer and slower on very large ranges, so it should be used only when case truly matters, such as when codes differ only by capitalisation.
COUNTIF compared with COUNT, COUNTA, and SUMPRODUCT
Choosing the right function is half the task, so a comparison helps. COUNT counts cells containing numbers, including dates, which Excel stores as numbers. It ignores text, blanks, and errors. COUNTA counts all non-empty cells, including numbers, text, dates, and errors, but it ignores truly empty cells. COUNTIF counts cells in a range that meet one condition, which can be text, a number comparison, or a wildcard pattern. COUNTIFS counts cells meeting multiple conditions. SUMPRODUCT is a more general function that can count with complex logic, including case-sensitive comparisons and text tests, but its formulas are longer and harder to read. For the question in this guide, COUNTIF is the right starting point, COUNTIFS is the right next step, and SUMPRODUCT is the advanced fallback.
A table summarises the choice, but the real skill is recognising which question you are asking. If the question is how many cells contain numbers, use COUNT. If the question is how many cells are filled at all, use COUNTA. If the question is how many cells contain a particular word, use COUNTIF. If the question is how many rows match two or more conditions, use COUNTIFS. If the question is how many cells contain text, regardless of the word, use COUNTIF with a wildcard or a text test. Framing the question in those terms before writing the formula prevents most errors, because the formula then follows directly from the question.
| Function | What it counts | Use it when |
|---|---|---|
| COUNT | Numbers only | You need a numeric count, not text |
| COUNTA | All non-empty cells | You need to know how many cells are filled |
| COUNTIF | Cells meeting one condition | You need to count a specific word or pattern |
| COUNTIFS | Rows meeting all conditions | You need two or more conditions together |
| SUMPRODUCT with ISTEXT | Cells containing text only | Range mixes text and numbers and you need text only |
Related spreadsheet reading on this site includes a guide to counting text in a similar app: Google Sheets counting cells with text. For a different kind of counting, see this word-count style guide: emoji counted as words style guide. A third related read covers converting tables to text: converting table to text in Word.
Frequently asked questions about counting text in Excel
Why does COUNT return zero for a text column?
COUNT only counts numbers. Text cells are ignored by COUNT, even when the column is full. Use COUNTIF or COUNTA for text, depending on whether you need a specific word or any filled cell.
How do I count cells containing part of a word?
Use COUNTIF with wildcards. Place an asterisk before and after the fragment to match cells containing it anywhere. Remember that a broad pattern can match unintended entries, so test the pattern on a small sample first.
Why does my exact match count miss some entries?
The most common causes are extra spaces, different spelling, or a range that does not cover all rows. Check for trailing spaces, use TRIM in a helper column if needed, and verify the range endpoints.
How do I count with two conditions?
Use COUNTIFS, which takes pairs of ranges and criteria. It counts only rows where every condition is true. Make sure the ranges are the same size and aligned row by row.
Does COUNTIF care about capital letters?
No. COUNTIF matching is not case-sensitive. Yes and YES count as the same. For a case-sensitive count, use a SUMPRODUCT formula with EXACT instead.
Conclusion
How do you count text in excel, in the end, is a question with a simple core answer and a set of useful variations. The core answer is COUNTIF with a range and a text criteria in quotes. The variations cover partial matches with wildcards, multiple conditions with COUNTIFS, counting any text with a wildcard pattern, and handling blanks, spaces, errors, and case. The most common mistakes are using COUNT for text, choosing a range that is too small, overlooking hidden spaces, and using a wildcard pattern that is broader than intended. Avoid those, test formulas on a small sample before applying them to a large list, and keep a helper column for cleaned data when the source data is messy. With those habits, counting text in Excel becomes a reliable routine rather than a source of puzzling results, and the same COUNTIF pattern will serve for surveys, inventories, status lists, and many other everyday tables.
