Finding the right excel formula for counting text in cells saves a surprising amount of manual tallying. Whether you are counting how many orders say Shipped, how many survey answers contain the word happy, or how many cells in a column hold any text at all, a good formula gives you a live total that updates the moment the data changes.
Excel offers several counting tools, and most confusion comes from picking the wrong one. COUNT only counts numbers, COUNTA counts almost everything that is not empty, and COUNTIF counts cells that match a rule you set, which makes it the usual answer when text is involved. COUNTIFS extends the idea across several rules at once, and helper approaches based on LEN and ISTEXT cover the unusual cases.
This guide works through each excel formula for counting text in cells with a small example table, explains wildcards for partial matches, and finishes with fixes for the common reasons a count comes back wrong. The examples use a simple order list, so you can rebuild them in a minute and adapt the ranges to your own sheet.
COUNTIF: the core excel formula for counting text in cells
The workhorse excel formula for counting text in cells is COUNTIF, which takes a range and a criterion and returns how many cells match. To count cells that contain any text at all, the criterion is a single asterisk inside quotation marks, because the asterisk is a wildcard that stands for any sequence of characters. Written out, the formula is =COUNTIF(A2:A20,"*"), and it returns the number of cells in that range holding text, while ignoring numbers and truly empty cells.
Counting one specific word is just as direct. If a Status column holds values such as Shipped, Packed and Delayed, then =COUNTIF(B2:B20,"Shipped") counts exactly the shipped orders. The match is not case sensitive, so Shipped, shipped and SHIPPED in the data all count, which is usually what you want in a tidy list. Quotation marks matter here: text criteria go inside them, while cell references and numbers do not.
A small worked example
Imagine an order table with Status in column B, filled for ten orders. The statuses read Shipped, Packed, Shipped, Delayed, Shipped, Packed, Shipped, Cancelled, Packed and Shipped. The formula =COUNTIF(B2:B11,"Shipped") returns 5, =COUNTIF(B2:B11,"Packed") returns 3, and =COUNTIF(B2:B11,"*") returns 10, because every status cell holds text. Changing any status updates all three totals at once, the whole advantage of using an excel formula for counting text in cells instead of tally marks in a margin.
| Task | Formula pattern | What it returns |
|---|---|---|
| Count any text in a range | =COUNTIF(A2:A20,”*”) | Cells containing text of any length |
| Count one exact word | =COUNTIF(B2:B20,”Shipped”) | Cells matching that word in full |
| Count text containing a word | =COUNTIF(B2:B20,”*ship*”) | Cells where the word appears anywhere |
| Count everything except one word | =COUNTIF(B2:B20,”<>Shipped”) | Non-matching, non-empty cells |
| Count non-empty cells of any kind | =COUNTA(A2:A20) | Text, numbers, dates and results of formulas |
| Count text cells only, strictly | =SUMPRODUCT(–(ISTEXT(A2:A20))) | Cells Excel treats as text values |
Two close relatives are worth knowing. To count cells that do not match a word, use the not-equal sign pair inside the criterion, as in =COUNTIF(B2:B20,"<>Shipped"), remembering that this pattern also counts numbers. And a criterion can come from another cell, as in =COUNTIF(B2:B20,D2) where D2 holds the word, so changing one cell retargets the count.
COUNTA and COUNTIFS: widening the excel formula for counting text in cells
COUNTA answers a broader question than any excel formula for counting text in cells based on COUNTIF. It counts every non-empty cell in a range, including numbers, dates and formula results. Use =COUNTA(A2:A20) when you simply need to know how many entries a list has, for example how many names were typed into a sign-up sheet. Reach for the asterisk COUNTIF instead when numbers must be excluded from a text count.
One COUNTA trap catches many people: a cell that looks empty may contain a formula returning an empty string, and COUNTA counts it because the cell is not truly blank. If a total looks one or two higher than a manual count, such formulas are the likely explanation, and the strict ISTEXT method later gives a cleaner answer.
COUNTIFS is the multi-rule version, and it turns an excel formula for counting text in cells into a small report. Each extra condition adds a range and a criterion, and a row counts only when every condition holds. For example, =COUNTIFS(B2:B20,"Shipped",C2:C20,"North") counts orders that are both shipped and in the North region. A third pair, such as a month column matching January, refines the count further, and all ranges must be the same size.
Useful COUNTIFS patterns
Regional and category breakdowns are the natural home of COUNTIFS. Build a grid with regions down one side and statuses across the top, place one COUNTIFS formula in each grid cell, and a full summary builds itself from the raw list. Because the criteria can point at the grid headings, one formula copies across and down the whole table, far safer than writing a separate excel formula for counting text in cells for every combination.
Wildcards and partial matches
Real data is messier than a tidy status list, and wildcards make an excel formula for counting text in cells cope with the mess. The asterisk stands for any number of characters, including none, so =COUNTIF(A2:A20,"*ship*") counts cells containing ship anywhere inside longer text, catching Shipped, Shipping soon and Shipment delayed in one sweep. Placing the asterisk only at the end, as in "ship*", limits the count to cells beginning with those letters.
The question mark wildcard stands for exactly one character. A pattern such as "????" counts text entries of exactly four characters, which can help audit codes or short answers. If the data contains a real asterisk or question mark to match literally, place a tilde directly before it in the criterion, telling Excel to treat that symbol as an ordinary character.
Partial matching deserves one warning. An excel formula for counting text in cells that uses asterisks on both sides counts a cell once even if the word appears twice inside it, because COUNTIF counts cells, not occurrences. Counting occurrences is a different job, handled with LEN in the next section.
LEN-based counts and the strict ISTEXT method
LEN measures the length of text, so it powers a family of counting tricks no other method can match. To count the total number of characters held in a range, use =SUMPRODUCT(LEN(A2:A20)), which adds the length of every cell. To count how many times a short piece of text appears across a range, compare lengths before and after removing that piece with SUBSTITUTE: the drop in total length, divided by the length of the piece, gives the number of occurrences, including repeats inside a single cell.
The LEN approach also produces a strict filled-text check. The formula =SUMPRODUCT(--(LEN(TRIM(A2:A20))>0)) counts cells holding at least one visible character after spaces are trimmed away, so cells containing only spaces, which COUNTIF with an asterisk would count as text, are correctly left out. Data pasted from other systems often hides such cells, and finding them explains many a mismatched tally.
ISTEXT when text only will do
Sometimes a column mixes numbers, dates and labels, and only the true text entries should be counted. The formula =SUMPRODUCT(--(ISTEXT(A2:A20))) handles this neatly: ISTEXT tests each cell, the double minus converts the true and false results into ones and zeros, and SUMPRODUCT adds them. It is the most literal excel formula for counting text in cells on this page, because it asks Excel directly whether each value is text, and it also exposes numbers stored as text, which count as text here and can then be cleaned up.
Fixes when a count comes back wrong
When an excel formula for counting text in cells disagrees with your eyes, work through a short checklist before rewriting anything. First, confirm the range covers the whole list, because a count over A2:A20 never sees a new entry in row 21. Second, look for leading or trailing spaces in the data, since padded and space-only entries quietly distort COUNTA and LEN results, and TRIM based checks reveal them.
Third, check for numbers stored as text, a common result of importing data. Fourth, remember that exact-word COUNTIF criteria match the whole cell, so a cell reading Shipped today will not be counted by a search for Shipped alone; switch to asterisks on both sides when the word can sit inside longer entries. Finally, if totals look stale, check whether calculation has been set to manual before doubting the formula itself.
A final habit keeps future sheets honest: point every new excel formula for counting text in cells at a tiny test block with known values, like the ten-order example earlier, before trusting it on real data. A formula that returns 5, 3 and 10 on the test block will behave on a thousand rows.
Frequently asked questions
What is the simplest excel formula for counting text in cells? The simplest is COUNTIF with an asterisk criterion, written as =COUNTIF(A2:A20,”*”), which counts every cell in the range that contains text. For one specific word, replace the asterisk with that word, keeping the quotation marks, and the same formula counts exact matches instead.
What is the difference between COUNTIF, COUNTA and COUNT? COUNT counts only numbers and dates. COUNTA counts every non-empty cell, including text, numbers and formula results. COUNTIF counts cells that meet a rule you set, which is why an excel formula for counting text in cells is usually built on COUNTIF when the count depends on what the text says.
How do I count cells that contain a word inside longer text? Surround the word with asterisks in the criterion, as in =COUNTIF(A2:A20,”*ship*”). The asterisks act as wildcards standing for any other characters, so cells such as Shipping soon are counted. Remember the result counts matching cells once each, even if the word appears twice in the same cell.
Why is my count higher than the number of entries I can see? The usual causes are cells containing formulas that return an empty string, entries hidden by extra spaces, or a range that includes a header or stray value. Test with the ISTEXT formula or a LEN and TRIM check to count only cells holding real visible text, and the difference usually identifies itself quickly.
Conclusion
Most counting jobs yield to one pattern: COUNTIF with a word for exact matches, COUNTIF with asterisks for partial matches, COUNTA for non-empty totals, COUNTIFS when conditions must hold together, and LEN or ISTEXT for strict checks on messy data. Learn those five shapes and every excel formula for counting text in cells you are likely to need is in your hands, ready to adapt by changing only the range and the criterion.
To build on these skills, try the related guides to Excel COUNTIF when a cell contains text, another look at the Excel formula to count text, and this walkthrough on how to combine text from two cells in Excel.
