Alym LabsExcel for Accounting
← All functionsTaught in Unit 20 · The COUNTBLANK function
Checking function

COUNTBLANK

How many cells were left empty.

Purpose

Count the gaps in a column before you report off it

Returns

A number: how many cells in the range are empty

Syntax

=COUNTBLANK(range)

ArgumentRequiredWhat goes here
rangeRequiredThe block to check for gaps, like B2:B13.

How to use COUNTBLANK

COUNTBLANK counts the cells with nothing in them. On a column that should be complete, the only acceptable answer is nought.

This is the check that catches the failure a total cannot: a missing amount does not make a sum wrong in any visible way, it just makes it smaller than the truth.

Run it before you report a figure, not after somebody queries one.

It is also the clearest place to see what a trimmed range does. Written over a generous block, COUNTBLANK counts every empty row below the data as a gap, which tells you nothing. Trim the range and it counts the real ones.

Examples

Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.

Supplier import — raw.xlsxSheet1
A supplier export before anyone has cleaned it: padded names, a code buried in a reference, and amounts that arrived as text
ABCD
1ReferenceSupplier nameAmountPosted
2GL-6100-8801 brightwell paper 1500Y
3GL-6200-8802calder UTILITIES320y
4GL-6100-8803meridian freight ltd2750
5GL-6300-8804 ashgrove catering184Y
6GL-6200-8805THORNBURY print760
7GL-6100-8806Brightwell Paper1120Y
FormulaResult
=COUNTBLANK(D2:D7)Two rows have not been posted. Worth knowing before the ledger is closed.2
=COUNTBLANK(C2:C7)Nought, so every row has an amount. That the amounts are text is a different question, and ISTEXT is the one that asks it.0
=COUNTBLANK(D2:D40)The same check written over a generous block, so next month can be pasted underneath. Now the empty rows below the data are counted as gaps and the answer is useless.35
=COUNTBLANK(D2:.D40)The dot trims the empty rows off the end of the range before it is counted, so the block can still grow and the answer is the real one again.2

Notes

  • A cell holding "" from a formula is counted as blank by COUNTBLANK, even though ISBLANK calls it FALSE.
  • COUNTBLANK takes one range only. Ask about two columns with two formulas.
ISBLANKIs this cell empty?COUNTACount the cells that are not empty.COUNTCount the cells holding numbers.TRIMRANGECut the empty rows off a range.
Practise COUNTBLANK in Unit 20

A reference tells you what it does. A unit makes you use it.