COUNTIFS function
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.
- Available in
- Every current version
- Returns
- The number of rows where all the conditions are met.
- Google Sheets
- Also available in Google Sheets.
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 below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| 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" |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Region | Status | Amount | Check | Result | ||
| East | Shipped | $820 | East and shipped | 3 | ||
| West | Pending | $450 | $500 to $999 | 4 | ||
| East | Pending | $1,300 | East or West | 6 | ||
| East | Shipped | $640 | ||||
| North | Shipped | $980 | ||||
| West | Shipped | $1,150 | ||||
| East | Shipped | $300 | ||||
| South | Pending | $720 |
Practice COUNTIFS
From lesson 8, COUNTIFS, SUMIFS & AVERAGEIFS. Graded instantly, then re-checked on changed data.
Count with a threshold
G6, count orders in the region from G2 with 50 or more units.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.
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.
Lessons that use COUNTIFS
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.