To count cells that contain something in Excel, use =COUNTA(A2:A20), replacing A2:A20 with the range you want to check. COUNTA counts numbers, text, dates, errors, and formulas—even formulas that display an empty string—so the right choice depends on what you mean by “has a value.”
What COUNTA counts
COUNTA is the usual answer when “has a value” means that a cell contains an entry rather than being genuinely empty. It counts cells containing numbers, text, dates, logical values such as TRUE, error values, and formulas. A date is stored as a number in Excel, but COUNTA counts it just as it counts other populated cells.
For example, with entries in A2:A20, the formula returns the number of cells in that range that Excel considers nonempty. It does not add the values together; it only counts the cells. If there are 19 cells in the range and 4 are empty, the result is 15.
COUNTA does not judge whether an entry is useful. A cell containing a single space, an accidental character, or a formula that displays no visible result can still count. This is the main reason a count may seem higher than the number of visibly filled cells.
Enter the formula in Excel
In Microsoft Excel for Microsoft 365 on Windows, select the cell where you want the count, type the formula, and press Enter. You can type the range address yourself or select the cells with the mouse while editing the formula.
- Choose an empty cell for the result, ideally outside the range being counted.
- Type an equals sign and the function name, followed by an opening parenthesis.
- Select or enter the range, add the closing parenthesis, and press Enter.
With the cells to check in A2:A20, enter =COUNTA(A2:A20). If you insert more data later, adjust the range or use a suitably bounded range that includes the new rows. A formula placed inside the range it counts can also include its own result cell, so keeping the total outside the counted range helps avoid an incorrect count or a circular reference.
The same formula works in Excel for Mac and Excel for the web. The menus and keyboard shortcuts for entering a formula are not important here; the function syntax and comma-separated arguments are the same in these versions.
A small example and its result
Suppose A2:A6 contains the following: “North,” a blank cell, the number 12, a formula that displays an empty string, and the date 1/15/2026. There are four cells with stored content or a formula, even though one of them looks blank on the worksheet.
With those entries in A2:A6, the result of =COUNTA(A2:A6) is 4. The text, number, formula, and date each count once; the genuinely empty cell does not. The displayed-empty formula counts because COUNTA sees the formula in that cell, not just what appears on screen.
This distinction matters in worksheets built from formulas. A report may use formulas to hide results until a condition is met. Those cells can look empty but still contribute to COUNTA, which can make a summary total appear unexpectedly large.
Count cells that meet a condition
If you want to count only cells matching a particular value or rule, use COUNTIF instead of COUNTA. COUNTIF takes a range and one criterion. The criterion can be a text label, a number, or a comparison written as text.
With payment statuses in B2:B20, this formula counts cells whose value is exactly Paid: =COUNTIF(B2:B20,"Paid"). It does not count other text such as Pending. Text criteria are enclosed in straight double quotation marks.
With scores in C2:C20, this formula counts scores greater than or equal to 70: =COUNTIF(C2:C20,">=70"). The comparison operator and number go together inside the quoted criterion. For a criterion based on a value in another cell, join the operator to that cell reference with an ampersand.
With scores in C2:C20 and a cutoff in E2, this formula counts scores at least as large as the cutoff: =COUNTIF(C2:C20,">="&E2). The ampersand joins the text operator to the value in E2. This approach lets you change the cutoff without editing the formula.
Count text, numbers, or blanks instead
COUNTA counts many data types together. If you need a narrower count, Excel has functions for specific kinds of cells:
- Numbers only: With values in A2:A20,
=COUNT(A2:A20)counts numeric entries, including dates and times. It does not count text, genuinely blank cells, or numbers stored as text. - Blank cells: With the same range,
=COUNTBLANK(A2:A20)counts empty cells. It also treats formulas that return an empty string as blank. - Text only: With text or mixed data in A2:A20,
=COUNTIF(A2:A20,"*")counts text entries. It is not a substitute for COUNTA when numbers, logical values, or errors should count too.
These functions answer different questions. For instance, if A2:A20 contains 8 numbers, 5 text entries, 2 errors, and 5 blank cells, COUNTA returns 15, COUNT returns 8, and COUNTBLANK returns 5. A date is included in COUNT because Excel stores dates as serial numbers. A number typed with a leading apostrophe, or imported as text, is not included in COUNT.
When formulas look blank
A formula can return an empty string, often written as two quotation marks inside the formula. The cell then appears blank, but it is not an unused cell: it contains a formula. COUNTA includes it. By contrast, COUNTBLANK treats a formula returning an empty string as blank. That difference is intentional and can explain why two otherwise reasonable counts do not match.
Decide whether you want to count stored formulas or visible results. If every formula represents a populated record, COUNTA may be appropriate. If you want to count entries that visibly contain text or numbers, you may need to exclude formulas that return empty strings.
With text and numeric values in A2:A20, this formula counts nonempty text strings plus numeric values: =COUNTIF(A2:A20,"?*")+COUNT(A2:A20). The wildcard ? requires at least one text character, while * allows any additional characters; COUNT adds numeric values. This method does not include logical values or error cells, and a cell containing only spaces still counts as text. Choose it only when that definition matches your data.
Choose the right range and avoid common mistakes
A correct function can still give a surprising answer if its range is wrong. Check the first and last cells in the range, especially after copying a formula or inserting rows. A range such as A2:A20 counts 19 cells; it does not automatically include data entered in A21.
Common issues to check include:
- Counting the heading: If A1 contains a column label, use A2:A20 when you want records only. Including A1 adds one to the result.
- Invisible spaces: A cell containing a space is not empty. COUNTA counts it even though it may look blank. Cleaning imported data or removing stray spaces may be necessary.
- Numbers stored as text: COUNTA counts them, but COUNT does not. This can happen after copying data from another system.
- Formula-generated empty strings: These count with COUNTA, despite looking blank. Use the definition of “populated” that suits the task.
- Wrong separator: In the formulas here, there is one range argument, so no argument separator is needed. In functions with multiple arguments, this article uses commas, as required by the specified Excel setup.
If the count must grow as new records are added, consider converting the data range to an Excel table using Insert > Table. Tables expand as rows are added, and formulas that refer to a table column can follow the expanded data. This is often easier to maintain than repeatedly extending a fixed cell range, particularly in a workbook that receives regular updates.
Frequently Asked Questions
What is the Excel formula to count nonblank cells?
Use COUNTA with the cells you want to check. With entries in A2:A20, enter =COUNTA(A2:A20). Remember that cells containing formulas that display an empty string are included.
Does COUNTA count cells with a formula returning nothing?
Yes. COUNTA counts the formula as content even when its displayed result looks blank. COUNTBLANK, on the other hand, treats a formula returning an empty string as blank.
How do I count cells containing numbers only?
Use COUNT. With the values to check in A2:A20, enter =COUNT(A2:A20). It counts numeric values and dates, but excludes numbers stored as text.
How do I count cells that contain a specific word?
Use COUNTIF with the target range and the word as its criterion. With statuses in B2:B20, enter =COUNTIF(B2:B20,"Paid") to count exact matches for Paid.
Why is my count higher than the number of visible entries?
The range may include a heading, cells may contain spaces, or formulas may return empty strings. Check the formula’s range and inspect cells that look blank; COUNTA counts formulas and space characters even when they are not visibly meaningful.
Related
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel DATEDIF Formula Examples for Age and Date GapsExcel’s DATEDIF function calculates the number of complete years, months, or days between two dates: use =DATEDIF(A2,B2,"Y") for…
- Excel SUBTOTAL: Count Unique Values in Filtered DataExcel’s SUBTOTAL function cannot count unique values by itself. In Microsoft 365, combine UNIQUE and FILTER with SUBTOTAL…
- Excel EDATE With Days Instead of Months: What to UseNo. EDATE adds months, not days; if A2 contains 10-Feb-2024 and B2 contains 30, use =A2+B2 to get…
- Power Query Sort: How to Sort and Keep Rows in OrderTo sort data in Power Query in Excel for Microsoft 365 on Windows, open the query, select the…
- Excel Accounting Formulas Cheat Sheet for Daily BooksAn Excel accounting formulas cheat sheet starts with =SUM(range) for totals, a simple addition formula for running balances,…
- VLOOKUP Excel Example: Look Up Data Across Two SheetsTo look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.