# COUNTIFS function in Excel

> COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.

Source: https://spreadsheetpractice.com/functions/countifs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.
- **Syntax:** `=COUNTIFS(criteria_range1, criteria1, ...)`
- **Most common use:** `=COUNTIFS(A2:A9,"East",B2:B9,"Shipped")` returns 3 (Both conditions must be true on the same row)
- **Available in:** Every current version
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The number of rows where all the conditions are met.
- **Google Sheets:** Also available in Google Sheets.

## What it does

COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000. Each condition is a pair of a range and a criterion, and all the ranges must be the same size. To count rows that match one condition or another, add two COUNTIFS together.

## Syntax

`=COUNTIFS(criteria_range1, criteria1, ...)`

| Argument | Required? | What it is |
|---|---|---|
| `criteria_range1` | Required | The first range to check. |
| `criteria1` | Required | The condition for the first range, such as "East" or ">=500". |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | `=COUNTIFS(A2:A9,"East",B2:B9,"Shipped")` | 3 | Both conditions must be true on the same row |
| G3 | `=COUNTIFS(C2:C9,">=500",C2:C9,"<1000")` | 4 | Two conditions on the same column make a range |
| G4 | `=COUNTIFS(A2:A9,"East")+COUNTIFS(A2:A9,"West")` | 6 | Add separate counts for "this or that" |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Region | Status | Amount |  |  | Check | Result |
| 2 | East | Shipped | $820 |  |  | East and shipped | 3 |
| 3 | West | Pending | $450 |  |  | $500 to $999 | 4 |
| 4 | East | Pending | $1,300 |  |  | East or West | 6 |
| 5 | East | Shipped | $640 |  |  |  |  |
| 6 | North | Shipped | $980 |  |  |  |  |
| 7 | West | Shipped | $1,150 |  |  |  |  |
| 8 | East | Shipped | $300 |  |  |  |  |
| 9 | South | Pending | $720 |  |  |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The ranges are different sizes. Every criteria range must have the same number of rows and columns. |
| The count is too small | All conditions are joined with AND. For OR logic, add two COUNTIFS together. |
| Comparisons return 0 | Put the operator in quotes and join cell references: ">="&F2. |

## Tips

- Count by date range with two conditions on the date column: ">="&start and "<="&end.
- Use COUNTIFS with a single pair as a drop-in replacement for COUNTIF; the syntax then extends naturally.

## Related functions

- [COUNTIF](https://spreadsheetpractice.com/functions/countif): COUNTIF counts the cells in a range that meet one condition: tickets marked "Open", scores of 70 or more, product names containing "lamp".
- [SUMIFS](https://spreadsheetpractice.com/functions/sumifs): SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.
- [AVERAGEIFS](https://spreadsheetpractice.com/functions/averageifs): AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.
- Comparison: [COUNTIF vs COUNTIFS](https://spreadsheetpractice.com/functions/compare/countif-vs-countifs)

## Questions

### How do I count with multiple criteria in Excel?

Use COUNTIFS with one range-and-criterion pair per condition, for example =COUNTIFS(A2:A9,"East",B2:B9,"Shipped").

### Can COUNTIFS do OR logic?

Not in one call. Add separate COUNTIFS together, or use SUM(COUNTIFS(A2:A9,{"East","West"})).

### How many conditions can COUNTIFS take?

Up to 127 range-and-criterion pairs.

## Practice

A graded COUNTIFS exercise ("Count with a threshold") is on [the COUNTIFS page](https://spreadsheetpractice.com/functions/countifs). It is checked instantly, then re-checked on changed data.

Lessons that use COUNTIFS:

- [Lesson 8: COUNTIFS, SUMIFS & AVERAGEIFS](https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
