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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 counts the cells that hold a number.

In E2, type:

=COUNT(B2:B6)

Text and empty cells are not counted.

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 counts the cells that hold a number. In E2, type: =COUNT(B2:B6). Text and empty cells are not counted.

    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. How many numbers are in C2:C6?

  3. 3

    COUNTA counts every cell that has anything in it: numbers, words, anything except empty. In E4, count the filled cells in B2:B6 with COUNTA. Shape: =COUNTA(range).

    Hint. Same range, COUNTA.

  4. 4

    In E6, count the filled cells in C2:C6. That is how many items were signed off. Remember: COUNT cannot see text, COUNTA can.

    Hint. Column C, COUNTA.

  5. 5

    In E8, count the names in A2:A6 minus the numbers in B2:B6. Shape: =COUNTA(range)-COUNT(range). That is how many items still need a number.

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

  6. 6

    COUNTBLANK counts the cells that are completely empty. In E10, count the empty cells in B2:B6 with COUNTBLANK. Shape: =COUNTBLANK(range).

    Hint. Same range as before, new function name.