Count Non-Blank Cells in Excel and Google Sheets
Purpose
Counting how many cells in a range actually have something in them is one of the most common diagnostics in…
Counting how many cells in a range actually have something in them is one of the most common diagnostics in any spreadsheet workflow. How many rows in this column have been filled in? How many tasks have a completion marker against them? How many customer records have a phone number recorded? These are all questions about non-blank cells, and the COUNTA function answers them in one short formula. COUNTA counts everything that is not truly empty — text, numbers, dates, errors, and logical values all register — which is the definition of "has an entry" most people intuitively mean. This is the direct inverse of the count blank cells formula, and together the two always add up to the total cells in the range. Understanding this pair of formulas and the COUNTA function behind COUNTA is the foundation of every data-completeness report I build.
Syntax
✓ All versions
✓ Google Sheets
= Same syntax
=COUNTA(range)
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The cells to examine. Can be a single cell, a range like A2:A100, a multi-column range like A2:D100, or multiple disjoint ranges separated by commas. COUNTA counts every non-blank cell regardless of data type. |
How it works
COUNTA examines every cell in the range and adds one to the total for every cell that is not truly empty. A cell is not empty if it contains text, a number, a date, an error value, a logical value (TRUE or FALSE), or a formula that evaluates to any of those. Crucially, COUNTA also counts cells containing a formula that returns an empty string "". From COUNTA's perspective, a cell with a formula is not empty even if the visible output is nothing. This is usually fine but occasionally produces a higher count than you expect when your range contains IF formulas with "" in their false branch. To count only cells with visible content and exclude formula-returned empty strings, subtract a COUNTIF with empty-string criteria: =COUNTA(range)-COUNTIF(range,""). The behaviour difference between COUNTA and the narrower COUNT function is worth knowing — COUNT counts only numeric cells, so if your column mixes text and numbers, COUNT will understate and COUNTA will be accurate. The opposite direction is covered by the count blank cells formula using COUNTBLANK.
Examples
1
Count how many status cells in a column actually have a value entered, giving you the number of completed rows.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Status | Count Filled |
| 2 | 1 | Open | =COUNTA(B2:B11) |
| 3 | 2 | ||
| 4 | 3 | Closed | 6 |
| 5 | 4 | ||
| 6 | 5 | Pending | |
| 7 | 6 | Closed | |
| 8 | 7 | ||
| 9 | 8 | Open | |
| 10 | 9 | ||
| 11 | 10 | Closed |
Row 2: 6
— Six cells have a status entered: rows 1, 3, 5, 6, 8, and 10 (Open, Closed, Pending, Closed, Open, Closed). The other four rows (2, 4, 7, 9) are blank and not counted.
COUNTA accepts only the range argument — no criteria. It treats every non-blank cell the same regardless of what the cell contains. The total of this count (6) plus the blank count from COUNTBLANK (4) equals the 10 rows in the range.
2
Count how many tasks have been marked as Done in a completion column to measure progress.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Task Status | Count Done |
| 2 | 1 | Done | =COUNTA(B2:B11) |
| 3 | 2 | ||
| 4 | 3 | Done | 6 |
| 5 | 4 | ||
| 6 | 5 | Done | |
| 7 | 6 | ||
| 8 | 7 | Done | |
| 9 | 8 | Done | |
| 10 | 9 | ||
| 11 | 10 | Done |
Row 2: 6
— Six tasks have Done entered: rows 1, 3, 5, 7, 8, and 10. The four blank rows represent incomplete tasks.
If you only want to count cells that specifically say Done (not just any entry), use COUNTIF(B2:B11,"Done") instead. COUNTA counts any non-blank value, which may or may not be what you need depending on how the column is used.
3
Use COUNTIF with a not-equal-to-blank criteria as an alternative to COUNTA, producing the same non-blank count on the same dataset.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Status | Count Not Blank |
| 2 | 1 | Open | =COUNTIF(B2:B11,"<>") |
| 3 | 2 | ||
| 4 | 3 | Closed | 6 |
| 5 | 4 | ||
| 6 | 5 | Pending | |
| 7 | 6 | Closed | |
| 8 | 7 | ||
| 9 | 8 | Open | |
| 10 | 9 | ||
| 11 | 10 | Closed |
Row 2: 6
— COUNTIF with the <> (not-blank) criteria returns 6 — the same non-blank count as COUNTA produced in Example 1 on the identical dataset.
Both COUNTA and COUNTIF(range,"<>") produce the same result in almost every scenario. COUNTA is the conventional and preferred choice because it is shorter, clearer in intent, and does not require the criteria-string syntax.
Common use cases
1. Count how many rows in a dataset have any entry in a required column for completeness reports
=COUNTA(A2:A500)
2. Count completed tasks by counting the non-blank checkmarks in a status column
=COUNTA(C2:C200)
3. Count responses in a survey where each response is any non-blank answer
=COUNTA(D2:D1000)
4. Count non-blank cells across multiple columns in a multi-field submission
=COUNTA(A2:E200)
5. Count non-blanks in two separate columns combined using a disjoint range
=COUNTA(A2:A100,C2:C100)
Common errors
Count is higher than expected
The range contains formulas that return empty strings (""). COUNTA counts formulas as non-blank even when the visible output is empty.
Fix: Use =COUNTA(range)-COUNTIF(range,"") to subtract formula-returned empty strings from the total.
COUNTA counts cells you expected to be blank
The cells contain a single space or a non-printing character. COUNTA treats those as non-blank because the cell has content.
Fix: Clean the data with TRIM and CLEAN, or use COUNTIF with pattern matching to identify the cells containing only whitespace.
#NAME? error
The function is spelled COUNT A (with a space) or is confused with COUNT.
Fix: Use COUNTA with no space. Remember COUNTA counts everything non-blank while COUNT counts only numbers.
Tips and variations
Combine with COUNTBLANK to verify totals always add up correctly
COUNTA and the count blank cells formula using COUNTBLANK are complementary — together they equal the total rows. If they do not, there is likely a formula returning an empty string that both functions are counting differently. This is a handy invariant check.
=COUNTA(A2:A100)+COUNTBLANK(A2:A100)
Use COUNTA across disjoint ranges for multi-column audits
COUNTA accepts multiple range arguments separated by commas. This is useful when you want to total non-blanks across non-adjacent columns without including the columns between them, which the COUNTA function handles transparently.
=COUNTA(A2:A100,C2:C100,E2:E100)
Divide by total rows to get a completion percentage
The ratio of COUNTA to total rows is a completion rate. Pair with the count blank cells formula and conditional formatting to build a data-quality dashboard — this is a standard pattern in any submission or response tracker I build.
=COUNTA(A2:A100)/ROWS(A2:A100)
Excel vs Google Sheets
Excel vs Google Sheets
COUNTA works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The behaviour — counting every non-blank cell regardless of data type — is the same in both applications. Formulas copy between them without any changes.
Related reading
Frequently asked questions
Use =COUNTA(range). For example, =COUNTA(A2:A100) returns the number of cells in A2:A100 that are not empty. COUNTA counts every cell containing text, numbers, dates, errors, or logical values — anything other than truly empty. The same function works in Google Sheets.
COUNT counts only cells containing numbers (including dates, which are stored as numbers). COUNTA counts every non-blank cell regardless of its data type — text, numbers, dates, errors, and logical values all count. If your data is a mix, COUNTA gives a higher number than COUNT.
Yes. If a cell contains =IF(A1>10,"High","") and the result is "", COUNTA still counts it because the cell technically contains a formula and its output is not considered truly empty. If you want to exclude formula-returned empty strings, use =COUNTA(range)-COUNTIF(range,"") or use SUMPRODUCT with LEN.
Pass a multi-column range or multiple ranges: =COUNTA(A2:C100) counts non-blank cells across a 3-column range. For disjoint ranges, separate them with commas: =COUNTA(A2:A100, C2:C100) counts non-blanks in two specific columns while ignoring the column between them.
Usually yes, but with an edge case. COUNTA counts every non-blank cell including those with formulas that return "". COUNTIF(range,"<>") also counts cells with formula-returned "" in most cases. In practice they return the same number. COUNTA is shorter, clearer, and the conventional choice.
Related Functions