COUNT, COUNTA and COUNTBLANK practice exercises
Count cells: COUNT counts numbers, COUNTA counts anything that is not empty, and COUNTBLANK counts the gaps.
How COUNT, COUNTA and COUNTBLANK work
Excel has three basic counting functions, and each one counts something different. COUNT counts the cells that hold a number (dates and times count as numbers too). COUNTA counts every cell that is not empty, whatever is in it. COUNTBLANK counts the empty ones. All three take a range.
Say B2:B7 holds a list of orders where some rows have a note instead of a number. =COUNT(B2:B7) tells you how many have a number of units. =COUNTA(B2:B7) counts every row that has anything in it, notes included. =COUNTBLANK(B2:B7) counts the rows with nothing at all.
The most common mix-up is using COUNT on text. It ignores names, emails and notes, so it returns 0 for a column of text. Use COUNTA there. Numbers stored as text trip people up too, because COUNT skips them while COUNTA counts them. To count only the cells that meet a condition, you need COUNTIF instead. The exercises below cover counting numbers, filled-in cells and empty cells.
- 1Count numbers with COUNTSome orders have a note instead of a number. In B8, use COUNT to count how many orders in B2:B7 have a number of units.
- 2Count filled-in cells with COUNTAIn E2, use COUNTA to count how many people in B2:B7 have given an email address.
- 3Count empty cells with COUNTBLANKIn E2, use COUNTBLANK to count how many phone numbers are missing in B2:B7.
Functions in this topic
Read the guide
Questions
How do I count cells with a condition in Excel?
Use COUNTIF for one condition: =COUNTIF(B2:B10,"North") counts the cells in B2:B10 that say North. Numbers work with operators, like =COUNTIF(B2:B10,">100"). For two or more conditions at once, use COUNTIFS, which takes a range and a criterion for each condition.
Why does COUNT return 0 when I can see numbers?
The numbers are most likely stored as text, and COUNT only counts real numbers. They sit on the left side of the cell and often show a green triangle. Convert them to numbers, or use COUNTA if all you need is the number of filled-in cells.
Why does COUNTA count cells that look empty?
A cell with a formula that returns "" or a single space is not empty, so COUNTA counts it. To count only cells that show something, use =SUMPRODUCT(--(LEN(B2:B10)>0)). It counts the cells whose content has at least one character. A lone space still counts, so clean those out first.