Learning how to google spreadsheet count number of cells with text is one of those small spreadsheet skills that saves a surprising amount of time. Whether you are tidying a list of names, checking how many survey answers have been filled in, or auditing a shared sheet before a report, you usually need a simple answer: how many cells in this range actually contain words, rather than numbers, dates, or blanks? Google Sheets has several ways to get that answer, and the right formula depends on exactly what you mean by “text”. This guide explains the main methods step by step, shows how they differ, and helps you avoid the common traps, such as counting blank-looking cells, numbers stored as text, and cells that contain only spaces.
The examples below use ordinary ranges such as A2:A100. You can adapt them to any range by changing the letters and numbers. All formulas are typed into a cell in Google Sheets, starting with an equals sign, and they update automatically when the data changes. No add-ons are needed, and no data leaves your spreadsheet.
Google Spreadsheet Count Number of Cells With Text: The Basics
When you google spreadsheet count number of cells with text, the first method most people meet is COUNTA. The COUNTA function counts every cell in a range that is not empty. That includes text, numbers, dates, tick boxes, and formula results, even a formula that returns an empty-looking result in some cases. It is useful as a quick “how full is this range?” check, but it is not a pure text count. If your range contains a mixture of names and numbers, COUNTA will overstate the number of text entries.
The formula is straightforward: =COUNTA(A2:A100). If 40 cells contain names, 10 contain numbers, and the rest are blank, COUNTA returns 50. That is correct for “non-empty cells”, but not for “cells with text”. The distinction matters most in real sheets, where columns often mix data types. A column headed “Notes” may be mostly text, while a column headed “Responses” may contain numbers, short codes, and comments side by side.
For a true text count, the COUNTIF function with a wildcard is the usual answer. The formula =COUNTIF(A2:A100, "*") counts cells that contain text. The asterisk is a wildcard meaning “any sequence of characters”. In practice, Google Sheets treats a cell as text for this purpose when its content is a text string. Numbers, including numbers typed as 42, are not counted, because they are stored as numbers rather than as text. Dates are also numbers underneath, so they are not counted either. That behaviour is usually what people want when they ask how to google spreadsheet count number of cells with text.
Try the difference on a small test range before using a formula on a large sheet. Type a name, a number, a date, and a word with a trailing space into four cells, then compare COUNTA and COUNTIF with the asterisk. Seeing the two results side by side makes the distinction concrete, and it helps you explain the formula to colleagues who will reuse the sheet.
Google Spreadsheet Count Number of Cells With Text: Formulas That Work
The most reliable everyday formula is =COUNTIF(A2:A100, "*"). It is short, readable, and easy to audit. If you want to count only cells containing a particular word, you can adapt the pattern: =COUNTIF(A2:A100, "*approved*") counts cells containing the word “approved” anywhere in the text. The asterisks before and after the word allow other characters on either side. Without them, COUNTIF would only match cells whose entire content equals the word.
Another useful pattern is SUMPRODUCT combined with ISTEXT. The formula =SUMPRODUCT(ISTEXT(A2:A100)*1) checks each cell, returns 1 for text and 0 otherwise, and adds the results. It behaves similarly to COUNTIF with an asterisk, but it is more explicit about the test being applied, which some users prefer when documenting a complex sheet. It also avoids wildcard quirks in edge cases, such as cells that begin with an equals sign or an apostrophe.
For counting text across a whole column, you can use =COUNTIF(A:A, "*"). This counts every text cell in column A, including the header if the header is text. If you want to exclude the header, start at row 2, as in the earlier examples. Whole-column formulas are convenient, but they can slow a very large sheet, so for ranges of tens of thousands of rows it is better to limit the range to the data you actually have.
To count text in several columns at once, extend the range: =COUNTIF(A2:C100, "*") counts text cells across columns A, B, and C. If the columns should be counted separately, use one formula per column and add the results, or place the counts in adjacent cells so that each column’s total remains visible. Keeping counts visible per column makes errors easier to spot than a single combined total.
Counting Text With Extra Conditions
Often the real question is more specific: how many text entries are there for a particular category, month, or team? COUNTIFS handles that by combining conditions. For example, =COUNTIFS(A2:A100, "*", B2:B100, "Sales") counts text entries in column A where column B equals “Sales”. The first condition is the text test, and the second is the category filter. Both ranges must be the same size.
You can also count text entries that are not blank after trimming spaces, or combine a text test with a date range. The key habit is to build the formula one condition at a time and check each step. Add the text condition first, confirm the total, then add the category condition and confirm that the total falls as expected. If the total jumps unexpectedly, one condition is probably matching more than intended, often because of a wildcard or a spelling difference.
Numbers, Dates, and Blank-Looking Cells
The trickiest part of learning to google spreadsheet count number of cells with text is understanding what Google Sheets considers to be text. A number typed into a cell is stored as a number, even if it looks like a word to a human reader. A code such as 007, typed without an apostrophe, may be stored as the number 7 and displayed differently. A date is also a number underneath, formatted to look like a calendar date. Neither will be counted by COUNTIF with an asterisk.
Conversely, a number stored as text will be counted. This happens when a cell is formatted as plain text before typing, when data is imported from another system, or when a value is preceded by an apostrophe. You can spot numbers stored as text because Google Sheets often shows a small warning, and because such values align to the left by default, while true numbers align to the right. If your count seems too high, check for imported codes, postcodes, or identifiers that look numeric but are stored as text.
Blank-looking cells cause the opposite problem. A cell containing only spaces looks empty, but it is not empty: it contains text made up of spaces. COUNTIF with an asterisk will count it, because a space is a character. A cell containing a formula that returns an empty string, such as =IF(B2="","",B2), can also behave unexpectedly depending on the function used. COUNTA counts such formula cells as non-empty, while COUNTIF with an asterisk usually does not count a truly empty string result. When a count looks wrong by a small amount, spaces and formula results are the first things to check.
The TRIM function helps here. TRIM removes leading and trailing spaces and reduces repeated internal spaces to one. You cannot easily TRIM a whole range inside COUNTIF, but you can clean the data itself: select the range, use Find and Replace to remove stray spaces, or add a helper column with =TRIM(A2) and count the helper column instead. Cleaning data once is usually better than building an ever more complicated formula to work around messy input.
Worked Examples You Can Copy
Imagine a feedback sheet with responses in column C, rows 2 to 201. To count how many respondents wrote a comment, use =COUNTIF(C2:C201, "*"). To count how many left the comment blank, subtract that result from the total number of rows: =ROWS(C2:C201)-COUNTIF(C2:C201, "*"), remembering that this counts non-text entries as well as blanks, so check for numbers in the column first.
Imagine an attendance list with names in column A and a status in column B. To count named entries marked “Present”, use =COUNTIFS(A2:A100, "*", B2:B100, "Present"). To count all non-empty names regardless of status, use COUNTA instead. Keeping both formulas visible, labelled clearly, makes the sheet self-documenting: a colleague can see at a glance whether a total means “names entered” or “names marked present”.
Imagine a product list where column D contains either a description or a stock number. To count descriptions only, use the COUNTIF asterisk formula. To count stock numbers only, use =COUNT(D2:D100), which counts numbers. Together, the two formulas should add up to the COUNTA total if every cell contains either text or a number. If they do not add up, something in the column is neither, such as a date, a tick box, or an error value, and that discrepancy is itself useful information.
| Formula | What it counts | Best for |
|---|---|---|
=COUNTA(A2:A100) |
All non-empty cells | Checking how full a range is |
=COUNTIF(A2:A100, "*") |
Cells containing text | The everyday text count |
=SUMPRODUCT(ISTEXT(A2:A100)*1) |
Cells whose value is text | Explicit, auditable text tests |
=COUNT(A2:A100) |
Numbers only | Separating numbers from text |
=COUNTIFS(A2:A100, "*", B2:B100, "Sales") |
Text entries in a category | Filtered counts |
Common Mistakes and How to Fix Them
The most common mistake is using COUNTA when a text count is needed, which inflates the total whenever numbers or dates are present. The second is forgetting the header row, which adds one to a whole-column count. The third is case sensitivity: COUNTIF in Google Sheets is not case-sensitive for ordinary words, so “Approved” and “approved” match the same pattern. That is usually convenient, but if case matters, you need a different approach using EXACT inside SUMPRODUCT.
Another frequent error is a range that does not cover all the data, often because new rows were added below the formula’s range. If your data grows regularly, either extend the range generously, use a whole-column reference with the header excluded by subtraction, or convert the data into a structured table-like layout with a clear end row. A count that silently stops at row 100 while data continues to row 120 is worse than an obviously wrong count, because it looks plausible.
Spelling differences also break filtered counts. “Sales”, “sales “, with a trailing space, and “Sale” are three different values to COUNTIFS. Data validation dropdowns prevent most of this: if a column should contain only a small set of categories, restrict it to a list, and the counts will stay reliable. For free-text columns, accept that counts are approximate unless the data has been cleaned.
For general help with Google’s tools beyond Sheets, the site’s guide to translating with Google Translate shows the same habit of starting with a small test before applying a tool to a whole document. The same caution applies here: test a formula on a few rows, then scale it up.
Google Spreadsheet Count Number of Cells With Text: Frequently Asked Questions
What is the simplest formula to google spreadsheet count number of cells with text?
Use =COUNTIF(A2:A100, "*"), replacing the range with your own. It counts cells containing text and ignores numbers, dates, and truly blank cells.
Why does COUNTA give a higher number than COUNTIF?
COUNTA counts every non-empty cell, including numbers, dates, and tick boxes. COUNTIF with an asterisk counts text only, so it is lower whenever the range contains non-text values.
Does a cell containing only spaces count as text?
Yes, a space is a character, so a space-only cell is counted by COUNTIF with an asterisk. Clean such cells with TRIM or Find and Replace if they should be treated as blank.
How do I count text in one category only?
Use COUNTIFS, for example =COUNTIFS(A2:A100, "*", B2:B100, "Sales"), where column B holds the category and both ranges are the same size.
Can I count text across a whole sheet?
You can use a whole-column reference such as =COUNTIF(A:A, "*"), but remember to exclude or subtract a text header, and prefer a bounded range for very large sheets.
How often should I check a google spreadsheet count number of cells with text?
Check a google spreadsheet count number of cells with text whenever the data changes in a way that matters for a report: after an import, after a shared editing session, or before sending totals to someone else. A quick recount takes seconds and catches missing entries before they become errors in a summary.
Does google spreadsheet count number of cells with text include headers?
A google spreadsheet count number of cells with text includes a header if the header cell falls inside the counted range and contains text. Start the range at the first data row, usually row 2, when the header should be excluded from the total.
Why does my google spreadsheet count number of cells with text change after sorting?
A google spreadsheet count number of cells with text should not change after sorting alone, because sorting rearranges values without adding or removing them. If the total does change, a range probably no longer covers all the data, or a value was accidentally edited during the sort.
Can google spreadsheet count number of cells with text handle imported data?
Yes, a google spreadsheet count number of cells with text works on imported data, but imports are where numbers stored as text and stray spaces most often appear. After an import, test the count on a small sample before trusting a total for a whole sheet.
What is the difference between google spreadsheet count number of cells with text and counting words?
Google spreadsheet count number of cells with text counts cells, not words: a cell containing a whole sentence counts once, just like a cell containing a single name. Counting words inside cells needs a different formula based on splitting text, which is a separate task.
Conclusion
To google spreadsheet count number of cells with text, start with the question you actually need answered. If you need “how full is this range?”, COUNTA is enough. If you need “how many entries are words?”, COUNTIF with an asterisk is the right tool, with SUMPRODUCT and ISTEXT as a more explicit alternative. Remember that numbers and dates are not text, numbers stored as text are, and space-only cells can quietly inflate a count. Test each formula on a small range, label your totals clearly, and clean messy data rather than hiding it behind a complicated formula. With those habits, a text count becomes a reliable building block for reports, audits, and shared sheets, rather than a number you have to double-check every time. For a broader look at keeping online tools tidy, see also the site’s update guide and the Google name search guide, both of which reward the same test-first approach.