Excel Count Cells With Text: COUNTA, COUNTIF, and More

How to Count Cells With Text in Excel

You'd think Excel would have a simple function called COUNTTEXT — but it doesn't, and that's why this question comes up so often. People try =COUNT(A1:A100) on a column of names and get 0 back, which is confusing until you realise that COUNT specifically counts numbers, not text.

The function you actually need depends on exactly what you're trying to count: all non-empty cells (COUNTA), only text cells excluding numbers (COUNTIF with a wildcard), or cells containing a specific word (COUNTIF with that word). Each does something different, and picking the wrong one is the most common reason people get unexpected results from their counting formulas.

Counting cells that contain text in Excel is a surprisingly common need — and there's no single COUNT function that does it directly, which trips up a lot of people. The standard COUNT function only counts cells containing numbers. If you use =COUNT(A1:A100) on a column of names, it returns 0 because names are text, not numbers. To count cells with text, you need either COUNTA (which counts all non-empty cells), COUNTIF with a wildcard (which counts specifically text-containing cells), or another approach depending on exactly what you're trying to measure.

The right formula depends on your specific situation. Do you want to count all non-empty cells regardless of content type? Use COUNTA. Do you want to count only cells containing text (excluding numbers, dates, and blanks)? Use COUNTIF with the wildcard '*'. Do you want to count cells containing a specific word or phrase? Use COUNTIF with that text as the criteria. Do you want to count cells with text that meet multiple conditions? Use COUNTIFS. Each method answers a slightly different question about your data.

Getting this wrong usually means using COUNT when you should use COUNTA, or using COUNTA when you actually need COUNTIF. COUNT counts numbers only. COUNTA counts everything that isn't blank. COUNTIF counts cells matching a specific criterion — including text-specific criteria. Understanding which function matches your actual need prevents the frustration of formulas that return unexpected results. This guide covers all four methods with clear examples so you can pick the right approach for your data.

One context that makes text counting particularly useful: data quality checks. If a column should contain text entries (names, descriptions, status labels) for every row of data, counting the text cells and comparing that count to the expected number of rows instantly reveals missing entries. Similarly, counting cells with specific text values — how many entries say 'Complete' versus 'Pending' — provides quick status summaries without building pivot tables or complex reports.

Method 2: COUNTIF With Wildcards — Count Only Text Cells

When you need to count specifically text-containing cells — excluding numbers, dates, and blanks — COUNTIF with the asterisk (*) wildcard is the cleanest approach. The formula =COUNTIF(A2:A100,'*') counts only cells containing text strings. The asterisk matches any sequence of characters, so any cell with text matches the criterion. Numbers, dates, blank cells, and error values don't match the text wildcard.

This distinction matters when your column contains a mix of data types. A column that has names in some rows and ages in others would return different results for COUNTA (counts both names and ages) versus COUNTIF with '*' (counts only the name entries). If you're specifically asking 'how many text entries are in this column?' — not 'how many non-empty cells?' — the COUNTIF wildcard method gives you the precise answer.

The COUNTIF wildcard approach also handles a scenario that confuses many users: counting cells that contain any text at all in a mixed column. If column A has customer names in rows 2–30, order numbers (numeric) in rows 31–60, and blank cells in rows 61–100, =COUNTIF(A2:A100,'*') returns 29 — counting only the text name entries while ignoring both the numbers and blanks. No other single function provides this specific filtering behaviour with such simple syntax.

For counting cells that contain specific text, replace the wildcard with your search term. =COUNTIF(A2:A100,'Complete') counts cells containing exactly 'Complete' — the match is case-insensitive, so 'complete,' 'COMPLETE,' and 'Complete' all match. To count cells containing a word anywhere in the text (partial match), use wildcards around the word: =COUNTIF(A2:A100,'*apple*') counts any cell where 'apple' appears somewhere in the text — 'apple pie,' 'green apple,' and 'pineapple' would all match.

COUNTIF with text criteria handles most text-counting scenarios that COUNTA can't — and it's the formula you'll use most often when the question is specifically about text rather than just non-empty cells. The key is matching your criteria to your question: exact match (no wildcards), contains match (wildcards on both sides), starts with ('apple*'), or ends with ('*apple').

One important nuance: COUNTIF counts cells where the entire cell content matches the criteria. =COUNTIF(A2:A100,'Apple') matches a cell containing only 'Apple' but NOT a cell containing 'Apple Pie' (because the entire content isn't 'Apple'). For partial matching — counting cells that contain the word 'Apple' anywhere — you need the wildcards: =COUNTIF(A2:A100,'*Apple*'). This is a common source of confusion when COUNTIF returns fewer matches than expected.

Practical Examples of Counting Text in Excel

Real-world scenarios make text-counting formulas more concrete than abstract syntax descriptions. Here are several situations where counting text cells is the most efficient solution to a practical problem.

Survey response counting: you have a column where respondents typed 'Yes,' 'No,' or left the cell blank. =COUNTIF(B2:B200,'Yes') counts how many said Yes. =COUNTIF(B2:B200,'No') counts how many said No. =COUNTBLANK(B2:B200) counts how many didn't answer. Together, these three formulas give you a complete response breakdown without building a pivot table or chart. For percentage calculations, divide each count by COUNTA(B2:B200) to get the percentage of respondents who answered.

Status tracking: a project tracker has a Status column with entries like 'Not Started,' 'In Progress,' 'Complete,' and 'Blocked.' =COUNTIF(D2:D50,'Complete') tells you how many tasks are done. =COUNTIF(D2:D50,'Blocked') flags how many need attention. =COUNTIF(D2:D50,'In Progress') shows how many are active.

These counts form the basis of a dashboard without any complex formulas or macros — simple COUNTIF against a status column provides immediate project health metrics. You can even build a progress bar by using =COUNTIF(D2:D50,'Complete')/COUNTA(D2:D50) formatted as a percentage inside a cell with a data bar conditional formatting rule — giving a visual progress indicator driven entirely by the Status column's text entries.

Data quality checks: a customer database should have entries in every required field. =COUNTA(C2:C1000) compared to the expected row count reveals missing entries. =COUNTIF(C2:C1000,'*@*') counts cells containing an @ sign — a quick check for email address presence. =COUNTIF(E2:E1000,'*') compared to the total row count shows how many rows have text in column E versus how many are blank. These simple counts catch data quality issues that visual scanning would miss in large datasets.

Employee scheduling and attendance: a shift schedule has names in cells for assigned shifts and blank cells for unassigned shifts. =COUNTA(B2:B32) counts how many shifts are staffed across a month. =COUNTBLANK(B2:B32) counts unstaffed shifts. =COUNTIF(B2:B32,'John') counts how many shifts a specific employee is assigned. These counts produce scheduling summaries that update automatically as the schedule changes — no separate report needed.

Inventory categorisation: a product list has a Category column. =COUNTIF(F2:F500,'Electronics') counts electronics products. =COUNTIF(F2:F500,'*Furniture*') counts anything categorised as furniture (including subcategories like 'Office Furniture' or 'Outdoor Furniture'). Using these counts across all categories tells you how your inventory is distributed without sorting the data or building a separate summary.

Advanced Text Counting Techniques

Beyond basic counting, several advanced techniques handle more complex text-counting scenarios that simple COUNTIF can't address.

Counting unique text values — how many distinct text entries exist in a range — requires an array approach. =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) counts unique values (but fails if any cell is blank). For a blank-safe version: =SUMPRODUCT((A2:A100<>'')/COUNTIF(A2:A100&'',A2:A100&'')) handles empty cells correctly. In Excel 365, the simpler =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>''))) uses dynamic array functions to count unique non-blank entries.

Counting text cells by length is useful for data validation. =SUMPRODUCT((LEN(A2:A100)>50)*ISTEXT(A2:A100)*1) counts text cells with more than 50 characters — useful for identifying entries that exceed a field length limit. =SUMPRODUCT((LEN(A2:A100)=0)*(A2:A100<>'')*1) counts cells that contain formulas returning empty strings (look empty but aren't) — a diagnostic tool for finding phantom entries that inflate COUNTA counts.

Case-sensitive text counting requires EXACT inside SUMPRODUCT. =SUMPRODUCT(EXACT(A2:A100,'Apple')*1) counts cells containing exactly 'Apple' with matching case — distinguishing between 'Apple,' 'apple,' and 'APPLE.' Standard COUNTIF is case-insensitive, so this array approach is necessary when case matters (product codes, abbreviations, proper nouns where case carries meaning).

Counting text cells that start or end with specific characters is handled by positioning the wildcard. =COUNTIF(A2:A100,'Dr.*') counts cells starting with 'Dr.' — useful for identifying doctor titles in a name list. =COUNTIF(A2:A100,'*.pdf') counts cells ending with '.pdf' — useful for identifying file type references. The wildcard positioning determines the matching logic: start only, end only, contains anywhere, or exact match (no wildcards).

Counting text cells across multiple sheets can be done with 3D references for COUNTA: =COUNTA(Sheet1:Sheet12!A2:A100) counts non-empty cells in the same range across sheets 1 through 12. COUNTIF doesn't support 3D references directly, so for multi-sheet text-specific counting, you'd need to add individual COUNTIF formulas for each sheet: =COUNTIF(Sheet1!A2:A100,'*')+COUNTIF(Sheet2!A2:A100,'*')+... or use SUMPRODUCT with INDIRECT for a more dynamic approach.

Counting Text in Excel Tables and Filtered Data

When your data is in an Excel Table (Ctrl+T) or has filters applied, standard text-counting formulas may not behave as expected — and knowing the alternatives prevents incorrect results in filtered views.

COUNTA and COUNTIF count ALL cells in the specified range, including hidden rows. If you've applied a filter to show only 'Active' records but your COUNTIF formula counts all records (including hidden 'Inactive' ones), the count doesn't match what you see on screen. For filtered data, use SUBTOTAL instead: =SUBTOTAL(3,A2:A100) works like COUNTA but ignores hidden rows. There's no SUBTOTAL equivalent of COUNTIF, so for filtered text-specific counting, you'd need an array formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))*(A2:A100='Complete')).

In Excel Tables, structured references make formulas self-documenting. Instead of =COUNTIF(A2:A100,'Complete'), use =COUNTIF(Table1[Status],'Complete'). The structured reference [Status] automatically adjusts as the table grows — new rows are included without modifying the formula. This is one of the strongest arguments for converting data to Tables before applying counting formulas: the formulas never go stale when data is added.

Dashboard formulas that reference filtered tables should be built with awareness of whether the count should reflect the filtered view or the full dataset. Summary cells at the top of a dashboard typically show full-dataset counts (COUNTIF on the full table column), while in-context counts within the filtered area should use SUBTOTAL-based approaches. Mixing these two approaches — full-count in one cell and filtered-count in another — creates confusing dashboards where numbers don't appear to add up. Be explicit about which approach each formula uses.

Another consideration for Table-based counting: when you delete rows from a Table, the structured reference automatically shrinks to exclude the deleted rows. This means your counts adjust without any formula modification — unlike range-based formulas where deleting rows might leave the formula referencing rows that no longer contain data (producing a count that's too low if the range shrinks) or that now contain different data (producing a count that's too high if data was rearranged). This self-adjusting behaviour is one of the strongest arguments for using Tables in any dataset where rows are regularly added or removed.

Combining Text Counts With Other Formulas

Text counts become more powerful when combined with other Excel formulas to create calculated metrics, conditional alerts, and automated summaries.

Percentage calculations using text counts are straightforward: =COUNTIF(B2:B100,'Yes')/COUNTA(B2:B100) gives the percentage of 'Yes' responses out of all responses. Format as Percentage to display as a percentage rather than a decimal. For multi-option surveys, this pattern — COUNTIF for each option divided by the total COUNTA — gives you a complete percentage breakdown that updates automatically as new responses are added.

Conditional formatting driven by text counts highlights cells based on how many matching entries exist. If you want to highlight any status value that appears fewer than 3 times in the Status column (rare statuses that might be data entry errors), set a conditional formatting rule with the formula =COUNTIF($D:$D,D2)<3 on the Status column. Cells with rare values light up for review.

IF statements using text counts create automated status indicators. =IF(COUNTIF(E2:E50,'Blocked')>0,'Action Required','All Clear') checks whether any task is blocked and displays a warning message. =IF(COUNTIF(C2:C100,'*')

Data validation summaries that use text counts are particularly useful in workbooks that multiple people edit. If you have a data entry sheet where different team members enter information, a summary row at the top showing =COUNTA(B2:B500)&' of '&(ROW(B500)-1)&' rows filled' gives an instant progress indicator — '237 of 499 rows filled' — that everyone can see. Adding =COUNTIF(B2:B500,'*ERROR*') as an error counter flags rows that someone has marked for review, creating a self-documenting worksheet that communicates its own status without anyone needing to send update emails or check in manually on data entry progress.

This kind of formula-driven communication is one of the most underappreciated productivity gains in shared Excel workbooks — the spreadsheet itself becomes the status report, updated in real time as new entries are made by any team member who has the file open. It turns passive data storage into active communication — which is what well-designed spreadsheets should do.