COUNTIFS
Count the rows where every condition holds.
Count the rows where every condition holds
A number: how many rows match all the conditions at once
Syntax
=COUNTIFS(range1, condition1, [range2], [condition2], ...)
| Argument | Required | What goes here |
|---|---|---|
range1 | Required | The first column to test. |
condition1 | Required | What that column has to match. |
range2 | Optional | The next column to test. |
condition2 | Optional | What that column has to match. |
How to use COUNTIFS
COUNTIFS takes range and condition in pairs, and counts the rows where all of them hold. One pair behaves exactly like COUNTIF.
Every range has to cover the same rows. Testing C2:C9 against D2:D20 has no meaning and Excel refuses it rather than guessing which rows line up.
The conditions are joined by and, never by or. To count rows that are overdue or queried, add two COUNTIFS together, or count the complement instead.
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 |
|---|---|
=COUNTIFS(C2:C9,"Overdue",D2:D9,">30")Overdue and more than thirty days old. Both have to hold. | 3 |
=COUNTIFS(C2:C9,"Overdue")With one pair it is COUNTIF, which is worth knowing when you are not sure yet how many conditions there will be. | 5 |
=COUNTIFS(B2:B9,"Brightwell Paper",E2:E9,">500")One supplier, and only the invoices worth chasing. | 2 |
=COUNTIFS(C2:C9,"Overdue")+COUNTIFS(C2:C9,"Queried")How an or gets written: two counts added, because the conditions inside one COUNTIFS are always joined by and. | 6 |
Notes
- All the ranges must be the same size. This is the commonest reason a COUNTIFS refuses to calculate.
- Conditions inside one COUNTIFS are always joined by and.
Related
A reference tells you what it does. A unit makes you use it.