COUNTIF
Count the rows that match one condition.
Count the rows that meet one condition
A number: how many cells in the range match
Syntax
=COUNTIF(range, condition)
| Argument | Required | What goes here |
|---|---|---|
range | Required | The column to test, like B2:B13. |
condition | Required | What counts as a match, like "Pastry" or ">10". A * stands for any run of characters, a ? for one. |
How to use COUNTIF
COUNTIF takes the column to look at and what to look for. A condition is either a plain value, or an operator and a value inside one set of quotation marks: ">30", "<>Overdue".
The quotation marks around a comparison catch everybody once. >30 on its own is not a condition COUNTIF can read.
Referring to a cell rather than typing the value is what makes a summary block worth building: write the condition once against a label, and the block re-reads itself when the label changes.
Empty cells are never counted by a comparison, so a formula written over a generous block does not pick up the rows below the data.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=COUNTIF(C2:C9,"Overdue")How many invoices are overdue. A plain value needs no operator. | 5 |
=COUNTIF(D2:D9,">30")A comparison, with the operator inside the quotation marks. | 4 |
=COUNTIF(B2:B9,"Brightwell Paper")How many invoices this one supplier has open. | 2 |
=COUNTIF(C2:C9,"<>Overdue")Everything that is not overdue. Counting the complement is a good way to prove the first count was right, because the two have to add to the row count. | 3 |
Notes
- Text matching ignores capitals, so "overdue" and "Overdue" count the same rows.
- COUNTIF counts rows and never adds amounts. SUMIF is the one that adds.
Related
A reference tells you what it does. A unit makes you use it.