Level 1 · Count what's there with COUNTA, COUNTBLANK and COUNTIF

Before you add anything up, find out what the data holds. How many rows are there? How many of them are real transactions? What’s missing? Excel has a small family of counting functions for this, and each one counts something different.

COUNT, COUNTA and COUNTBLANK

Function	Counts	On this sheet
=COUNT(C2:C55)	cells holding a number	54 quantities
=COUNT(B2:B55)	(the product names are text)	0
=COUNTA(B2:B55)	cells that are not empty: text, numbers, anything	54
=COUNTBLANK(D2:D55)	empty cells	49

COUNT only counts numbers, so on a text column it returns 0. That is the classic slip when someone asks “how many rows?” and gets nothing back. For “how many rows”, use COUNTA on a column that is always filled, like the product name or an id.

COUNTA and COUNTBLANK are two halves of the same range. The coupon column has 5 filled cells (HOLIDAY20 on two orders) and 49 empty ones, and 5 + 49 = 54. When the two halves don’t add up to the row count, the range is the wrong size.

Lines are not orders

The sheet has one row per order line: order 12301 bought four products, so it takes four rows. George asked for both numbers, and they are different.

To count distinct orders in a sheet like this, flag the first time each order id appears. In E2:

E2   =IF(COUNTIF($A$2:A2,A2)=1,1,0)

The range $A$2:A2 has a locked start and a moving end. Filled down, row 5 reads $A$2:A5, row 9 reads $A$2:A9: it always runs from the top to the current row. So the COUNTIF asks “how many times has this order id appeared so far?” It is 1 only on the order’s first line, so the flag is 1 there and 0 on every repeat. Add up the column, =SUM(E2:E55), and you get the number of orders: 20. The same flag on column B counts 36 different products.

At your desk you may see two other ways. Microsoft 365 has =COUNTA(UNIQUE(A2:A55)), and older workbooks use =SUMPRODUCT(1/COUNTIF(A2:A55,A2:A55)). Both make Excel work on a whole list of values inside one cell. This practice sheet keeps one value per cell, so they don’t run here; the flag column gives the same answer and works in every version. Its bonus: you can see which rows counted, and filter on them.

Counting with a condition

COUNTIF counts the cells that pass one test. To count bulk lines, more than the 5 units in I1:

I7   =COUNTIF(C2:C55,">"&I1)

The operator is in quotes and the cell is joined with &, so the criterion becomes >5. Six lines pass: quantities of 6, 6, 8, 8, 9 and 12. The next levels take COUNTIF much further.

For “1 or 2 units”, one COUNTIF can’t test two values, but two of them added together can:

I8   =COUNTIF(C2:C55,1)+COUNTIF(C2:C55,2)

That’s 13 single-unit lines and 16 lines of two units: 29. A number criterion needs no quotes. Because quantities are whole numbers, =COUNTIFS(C2:C55,">=1",C2:C55,"<=2") gives the same 29.

Sanity-check the counts

• The quantity bands should add up: 29 small lines, 19 lines of 3 to 5 units and 6 bulk lines make 54.
• COUNT(C2:C55) should equal COUNTA(C2:C55). If it’s lower, some quantities were imported as text, and every sum of that column is too low.
• A distinct count can never be higher than the line count.

Common mistakes

• COUNT on text. =COUNT(B2:B55) returns 0. Use COUNTA.
• Reporting lines as orders. Count distinct order ids, not rows.
• An unlocked start in the flag. COUNTIF(A2:A2,A2) slides down as one cell, so every row counts 1 and the “orders” come out as 54.
• ">I1" in quotes. The criterion becomes the text “I1”; join the cell with &.
• “1 or 2” in one criterion. "1,2" matches nothing. Add two COUNTIFs.