criteria_range1 - Required. First range to evaluate (e.g., A:A, B2:B100). All criteria ranges must have same dimensions.
criterion1 - Required. Condition for first range (e.g., "East", ">100", "*Pro*", ">=2023-01-01").
criteria_range2, criterion2 - Optional. Optional. Additional range/criteria pairs. Up to 127 conditions supported.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Region | Quarter | Count |
| 2 | ORD-001 | East | Q1 | |
| 3 | ORD-002 | West | Q1 | |
| 4 | ORD-003 | East | Q2 | |
| 5 | ORD-004 | East | Q1 | |
| 6 | ORD-005 | East | Q1 | |
| 8 | East Region Q1: | =COUNTIFS(B:B, "East", C:C, "Q1") 3 |
Count rows where A="East" AND B="Q1"
Returns count of matching rows
Count where B≥100 AND C<1000
Numeric range filtering
Count where A contains "Pro" AND D="Active"
Text pattern + exact match
COUNTIF for single criteria, COUNTIFS for multi-criteria
Use COUNTIFS for complex filtering
Analyze transaction volume by combining geographic and time filters with the COUNTIFS function in Excel. It enables multi-dimensional counting impossible with single-criteria functions. Essential for sales managers analyzing regional performance across quarters, finance teams tracking transaction patterns, operations teams monitoring order volumes by location and period. The COUNTIFS function handles this two-way segmentation perfectly - count how many orders came from specific regions during specific quarters for accurate trend analysis and resource planning.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Region | Quarter | Amount |
| 2 | ORD-001 | East | Q1 | $25,000 |
| 3 | ORD-002 | West | Q1 | $32,000 |
| 4 | ORD-003 | East | Q2 | $28,000 |
| 5 | ORD-004 | East | Q1 | $18,000 |
| 6 | ORD-005 | West | Q2 | $41,000 |
| 7 | ORD-006 | East | Q1 | $22,000 |
| 8 | East Q1 Count: | =COUNTIFS(B:B, "East", C:C, "Q1") 3 |
Count employees meeting multiple criteria simultaneously with this powerful Excel function. HR teams use it to analyze workforce composition by department and compensation level, identify staffing gaps, plan promotions, and budget for raises. The function handles numeric ranges perfectly - count how many employees in Marketing earn between $50K and $75K, or how many Senior Developers have salaries above $100K. This multi-criteria counting is essential for workforce planning, compensation analysis, and DEI reporting.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Salary |
| 2 | Alice | Marketing | $65,000 |
| 3 | Bob | Sales | $82,000 |
| 4 | Carol | Marketing | $58,000 |
| 5 | Dave | Engineering | $95,000 |
| 6 | Marketing $50K-$70K: | =COUNTIFS(B:B, "Marketing", C:C, ">=50000", C:C, "<=70000") 2 |
❌ The Problem:
✅ Solution:
=COUNTIFS(A:A, "East", C:C, "Q1")Use this multi-criteria function in Excel to count rows where ALL conditions are true simultaneously. It applies AND logic across all criteria pairs, ensuring each row is counted only if it matches every condition. This is fundamentally different from adding separate COUNTIF results.
❌ The Problem:
✅ Solution:
=COUNTIFS(A:A, "East", B:B, "Q1")This function in Excel requires criteria_range and criterion pairs. Always provide both the range to evaluate AND the condition to test. Format: COUNTIFS(range1, criteria1, range2, criteria2, ...) where each range has a matching criterion.
❌ The Problem:
✅ Solution:
=COUNTIFS(A1:A10, "East", B1:B10, "Q1")Ensure all criteria ranges in this formula have identical dimensions. If checking rows 1-10 in column A, also check rows 1-10 in column B. The function evaluates each row across all ranges, so they must align perfectly for accurate results.
Master these COUNTIFS variants:
From basics to advanced - AI generates perfect formulas instantly.