Excel track · Exercise 21 of 86 Paid

Count functions summary

Tell counting values from counting rows, then use both together to find gaps.

Advanced

Parts list with a quantity column holding two gaps

Read-only data · screen 1 of 2 · answer in B10

In B10, count only the rows whose unit value is a number.

	A	B
1	Item	Units
2	Bolt	12
3	Nut	8
4	Washer
5	Bolt	5
6	Screw	n/a
7	Nut	3
8	Bolt	7
9	Washer	4
10	Rows with a number

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B10, count the rows that have an item but no numeric unit value.

	A	B
1	Item	Units
2	Bolt	12
3	Nut	8
4	Washer
5	Bolt	5
6	Screw	n/a
7	Nut	3
8	Bolt	7
9	Washer	4
10	Rows without one

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

The count family answers questions that read alike. COUNT counts numbers and passes over text and blanks. COUNTA counts every cell that holds anything, so a text value enters where it would not enter a sum.

The units column holds six numbers, a blank in B4 and the text n/a in B6, so COUNT returns 6 and COUNTA returns 7. Column A holds eight items, and eight minus six leaves the 2 rows where the item exists and the quantity does not.

That subtraction is how you screen an import before trusting a total. When COUNTA and COUNT agree, every row carries a number. When they do not, the gap is the count of rows to fix.

Syntax

=COUNT(range) counts numbers; =COUNTA(range) counts anything not empty

Worked example

On Parts list with a quantity column holding two gaps, =COUNT(B2:B9) returns 6.

=COUNT(B2:B9)

Common errors

Error codes for COUNT and COUNTA, and subtracting one from the other to find gaps
Error	What it means and how to fix it
`Wrong count`	COUNTA over B2:B9 answers 7, because the n/a counts as a value.
`Silent zero`	COUNT over a column that holds only text answers 0 and points at nothing.
`#NAME?`	A misspelled name such as COUNTT is not a function, and Excel says so.

At work

Checking how many rows of an import carry a real quantity before a pivot or a total is trusted.