How many orders came from the East region for notebooks in January? One condition is easy — COUNTIF handles that. Two or three conditions at once need COUNTIFS, and it answers questions like this in a single formula instead of a helper column and a filter. Sales reports, attendance lists, and inventory checks all run into the same problem: the answer you want is the count of rows that pass several tests at once, and COUNTIFS is the function built for exactly that.
What COUNTIFS does
COUNTIFS counts the rows where all of your conditions are true at the same time. Each condition is a range plus a rule, and you can add as many pairs as you need — region equals East, product equals Notebook, date in January. A row is counted only if it passes every single condition.
The syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- criteria_range1 — the first column to test, for example the Region column.
- criteria1 — the rule for that column, for example “East” or “>100”.
- criteria_range2, criteria2, … — more range-and-rule pairs. All of them must be true for a row to count.
Step-by-step example
Count the East-region notebook orders from this small sales list.
- Click the cell where the answer should go.
- Type
=COUNTIFS(A2:A13,"East",B2:B13,"Notebook")— the first pair tests the region, the second tests the product. - Press Enter. The result is the number of rows that match both conditions.
- Add a third pair to narrow it further, for example
C2:C13,">=2026-01-01"for January onward. - To count with a value from another cell, use
&to join it:=COUNTIFS(A2:A13,E2,B2:B13,"Notebook")where E2 holds the region.
Worked example
| Region | Product | Date | Amount |
|---|---|---|---|
| East | Notebook | 2026-01-05 | $120 |
| East | Pen set | 2026-01-06 | $85 |
| West | Notebook | 2026-01-07 | $200 |
| East | Notebook | 2026-01-12 | $95 |
=COUNTIFS(A2:A5,"East",B2:B5,"Notebook") returns 2 — the January 5th and January 12th rows. The Pen set row fails the product test and the West row fails the region test, so neither is counted.
Common errors
0 when you expected more. The most common cause is a criteria range that is a different size from the others. All ranges must have the same number of rows, or Excel returns a #VALUE! error — and a common silent cause is extra spaces in the data, so “East ” does not equal “East”.
Dates do not match. Dates stored as text are not dates. If a date condition returns 0, check the cells are real dates (right-aligned, and =ISNUMBER(C2) returns TRUE).
Quotes in the wrong place. Text criteria need quotes ("East") and comparisons need the operator inside the quotes (">100"). A cell reference must be joined with &, never placed inside the quotes.
It counts blank rows too. A criteria like "<>" counts everything that is not empty, including cells that only contain a space. If your data has stray spaces, clean it first with TRIM, or add a condition that excludes blanks explicitly.
Tip
COUNTIFS with a single pair is exactly COUNTIF. If you only ever need one condition, COUNTIF is shorter — but COUNTIFS scales to as many conditions as you like, so many people just use COUNTIFS for everything. One more trick: COUNTIFS can count dates in a range by combining two conditions on the same column, such as “>=” the first of the month and “<" the first of next month.
Practice file
Download the COUNTIFS practice file (.xlsx) — it contains a sales list, a summary sheet with COUNTIFS answers already filled in, and a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports COUNTIFS with the same syntax. The only difference is that Sheets also lets you use COUNTIFS inside ARRAYFORMULA for row-by-row counts.
Example prices and figures are illustrative, not market data.