=COUNTA(B2:B6) returns 3. The checklist has five steps, three of them carry a date and two are still empty. COUNTA counts every cell that has something in it, so it answers three. COUNT on the very same column answers 0, because those dates arrived as text rather than as date values.
Answer
=COUNTA(B2:B6) counts the cells in the range that are not empty. Text, numbers, dates, errors and stray spaces all count as filled; only a genuinely empty cell is skipped.
The counterpart question is why COUNT disagrees. COUNT counts numbers only, and a date that was pasted in as text is not a number to Excel even though it looks like one. On this column COUNT returns 0 where COUNTA returns 3, and that gap is the diagnosis: the column is text.
Why it works
- Empty means empty. A cell holding a single space is not empty, so COUNTA counts it, which is exactly how a progress bar ends up reporting a step that never happened.
COUNTAis the right function for a checklist, because the empty cell is the information: the step has not been done. Counting the filled ones is how the status deck gets its number without anyone scrolling.- Dates, numbers, labels and even
#N/Aall count. If the column might contain errors and you want to exclude them, COUNTA is the wrong tool, because it will happily report a column of#N/Aas full. - Counting from a whole column is safe if the header is empty, and not safe otherwise: a header row counts as a filled cell.
=COUNTA(B:B)-1is the usual correction, and it is the kind of arithmetic that goes wrong when a second header row is added. COUNTAanswers "how many rows have data", which is also the length of a list. That makes it the usual argument toINDEXwhen you are reading the last item of a column:=INDEX(B2:B100,COUNTA(B2:B100))reads the last filled cell.
SUBTOTAL(3,B2:B6) is the same count that respects a filter, so it changes when rows are hidden by one. When the number on screen has to match the rows the reader can see, that is the function to reach for.
What goes wrong
| What you see | What it means | The fix |
|---|---|---|
| A count higher than the steps done | a cell holds a space, an apostrophe or an empty string from a formula | =COUNTBLANK(B2:B6) shows how many are really empty, or clean the column |
| 0 from COUNT on the same column | the entries are text, so no cell is a number | count with COUNTA, or convert the column and count numbers |
| One too many on a whole column | the header row is filled, so it counts | count the data rows only, or subtract the header |
| A progress bar that never reaches 100% | the count is over a formula range that returns "" for unfinished rows | count the underlying values, not the display column |
#VALUE! | COUNTA was called with no argument at all | pass a range: =COUNTA(B2:B6) |
The first row is the reason to distrust a checklist that says 100%. A formula that returns an empty string is not empty as far as COUNTA is concerned. A column of =IF(A2="","",A2) counts as completely full no matter what the sheet looks like.
Practice
The exercise below is the five-step checklist with two blanks in it. It grades against the reference. Counting numbers instead of filled cells comes back with a message about what this column holds, and the second screen asks you to point at the right range.
Try it: exercise 1 of 1
In B7, count how many of the five steps have a date in B2:B6.
Two of the five cells are empty, so the answer is the other three.
Show the answer
Cell B7 holds 3Where to go next
| Page | Why it helps |
|---|---|
| COUNTIF | The counting note on this site: rows that match a value rather than every filled cell. |
| count only the numbers | The same column and the opposite question. |
| total one region's rows with SUMIF | The arithmetic version, for when you would add the matching rows rather than count them. |