Job task guide · Counting records

How to count order statuses

Order lists, support tickets, job applications, and inventory sheets usually have a status column: Pending, Shipped, Open, Closed, and so on. A common request is “How many are still pending?” Counting by eye works for ten rows but not for five hundred, and the answer is out of date as soon as a status changes. COUNTIF counts only the rows that match, and it updates on its own.

The example

An online shop lists eight orders. The order numbers are in column A and their statuses in column B. A small summary table in columns D and E will show how many orders have each status.

Row	A: Order	B: Status	D: Status	E: Count
2	1041	Pending	Pending	`=COUNTIF($B$2:$B$9, D2)` → 3
3	1042	Shipped	Shipped	→ 2
4	1043	Delivered	Delivered	→ 2
5	1044	Pending	Cancelled	→ 1
6	1045	Cancelled	Total	`=SUM(E2:E5)` → 8
7	1046	Shipped
8	1047	Pending
9	1048	Delivered

Step by step

Write the status labels you want to count in D2 to D5, spelled exactly as they appear in column B.
Click E2 and type =COUNTIF(.
Give the range to search: $B$2:$B$9. The dollar signs lock the range so it does not slide down when you copy the formula.
Type a comma, then the thing to match. You can type the text in quotation marks, as in "Pending", or point at the label cell, D2. Pointing at the cell means one formula works for every row.
Close the parenthesis and press Enter. E2 shows 3. Copy E2 down to E5 to count the other statuses.
Check the total. =SUM(E2:E5) should equal the number of orders, 8. If it is lower, some status in column B is spelled differently from your labels.

Common mistakes

Extra spaces or typos. “Pending ” with a trailing space or “Pendng” will not match “Pending”. Uppercase versus lowercase does not matter, because COUNTIF ignores case, but spelling and spaces do. The total check in step 6 catches this.
Missing quotation marks. =COUNTIF(B2:B9, Pending) without quotes makes the spreadsheet look for something named Pending, which gives an error or 0. Typed text inside a formula always needs quotation marks.
The range moves when copied. Without dollar signs, copying =COUNTIF(B2:B9, D2) down one row gives B3:B10, which misses order 1041.
Counting the heading. If the range starts at B1, the heading “Status” is included. It will not match any status, but it is a sign the range is off.

Totals by category with SUMIF

COUNTIF answers “how many rows?” Its partner SUMIF answers “how much in total for those rows?” Suppose column A lists a department for each expense and column B the amount. =SUMIF(A2:A20, "Sales", B2:B20) adds only the Sales amounts. The first range is checked for the label, and the last range supplies the numbers. Both ranges must start and end on the same rows so each label lines up with its amount.

Questions first-time users ask

Can I count with two conditions?

Yes, with COUNTIFS. To count orders that are Pending and belong to the North region in column C: =COUNTIFS(B2:B9, "Pending", C2:C9, "North").

Can I count numbers above a limit?

Yes. Put the comparison in quotation marks: =COUNTIF(F2:F9, ">100") counts the values greater than 100.

How do I count every filled row?

=COUNTA(B2:B9) counts all non-empty cells, whatever they say. It is a handy check against your status totals.

Does this work in Excel and Google Sheets?

Yes. COUNTIF, COUNTIFS, SUMIF, and COUNTA are written the same way in Excel, Google Sheets, and LibreOffice Calc.

Practice it

What you will see in the trainer

The longer order list above shows how a summary works. In Count Order Statuses, five statuses are already in B2:B6. Put =COUNTIF(B2:B6,"Pending") in E2 and =COUNTIF(B2:B6,"Shipped") in E3. The guided counts are 3 Pending and 2 Shipped.

In “Your turn,” the statuses change. Use the same two formulas in E2 and E3; the counts become 1 Pending and 4 Shipped. Summarize Department Spend is a separate lesson that uses SUMIF to add matching expenses.

Ready? Open Count Order Statuses or open Summarize Department Spend.