COUNTIFS function
Quick answer
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)
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
Answers that use COUNTIFS
In other languages
COUNTIFS is CONTAR.SI.CONJUNTO in Spanish Excel and ZÄHLENWENNS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .