Excel Practice
Lessons Lesson 5

COUNT and COUNTA

About this lesson

COUNT tells you how many cells in a range hold a number. COUNTA tells you how many cells hold anything.

By the end you can

  • count the numbers in a range with COUNT and the filled cells with COUNTA.
  • find how many rows are missing a figure by subtracting one from the other.
  • count the empty cells in a range with COUNTBLANK.

Practises COUNT COUNTA COUNTBLANK COUNTIF

The idea

These two functions become useful on a half-finished sheet, and most sheets are half-finished. A cell that says "not found" is invisible to COUNT. COUNTA sees it. A truly empty cell is invisible to both. This gives you a useful check. COUNTA minus COUNT is the number of cells with words instead of numbers. On a stock count, that is the number of rows with an excuse instead of a figure.

If the two functions give very different answers, it is not a rounding problem. It is a data problem, and now you know how big it is.

The mistake to watch for

Using COUNT on a column of names. COUNT counts numbers only, so a list of initials counts as nothing and looks like an empty column. COUNTA counts anything filled in; COUNTBLANK counts what is empty. Choose by what the column holds, not by the number you hope to see.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

COUNT tells you how many cells in a range hold a number.

In E2, type =COUNT(B2:B6).

It counts how many of the 5 items have a real number in the Counted column.

E2
Row A B C D E F G
1 Item Counted Checked by
2 Blue paint 5L 14 RS Numbers counted
3 White paint 5L 0 RS
4 Masking tape Cells filled in
5 Filler 2kg 6 MK
6 Brush set not found MK Items signed off
7
8 Still to count
9
10 Never started

Every step

  1. 1

    COUNT tells you how many cells in a range hold a number. In E2, type =COUNT(B2:B6). It counts how many of the 5 items have a real number in the Counted column.

    Hint. Equals, COUNT, the range in brackets.

  2. 2

    The Checked by column holds initials, never numbers. Read =COUNT(C2:C6) and say what it returns.

    Hint. Are initials numbers?

  3. 3

    COUNTA counts every cell that has anything in it: numbers, words, anything except empty. In E4, count the filled cells in the Counted column, B2:B6, with COUNTA.

    Hint. Same range, COUNTA.

  4. 4

    In E6, count how many items somebody signed off. The initials are in C2:C6. Use the function that can see text.

    Hint. Column C, COUNTA.

  5. 5

    In E8, write a formula with COUNTA and COUNT. It should show how many items still need a real number. That is the number of item names in column A, minus the number of figures in column B.

    Hint. COUNTA of the names, minus COUNT of the figures.

  6. 6

    There is a third one. COUNTBLANK counts the cells that are completely empty. In E10, count how many cells in B2:B6 have nothing in them at all.

    Hint. Same range. Use the function that counts empty cells.