COUNTIFS in Excel: what it does and how to use it
COUNTIFS counts the rows where every condition you give it is true at the same time. Conditions come in pairs: the column to check, then what to look for.
Syntax
COUNTIFS(criteria_range1, criteria1, ...)
criteria_range1- The first column to check.
criteria1- What that column has to contain.
criteria_range2, criteria2- More pairs, as many as you need. A row is counted only when all of them hold.
When to use it
Counting with more than one rule: open orders in one region, staff in one team on one contract, sales in a month above a set figure.
What trips people up
All the ranges must cover the same rows. A range that stops early gives a wrong count rather than an error, which makes it easy to miss. Whole columns such as A:A avoid it.
Watch it built, step by step
Questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes one condition; COUNTIFS takes up to 127 pairs. COUNTIFS with a single pair gives the same answer as COUNTIF, so some people use COUNTIFS everywhere and keep one habit.
How do I count rows that match one thing or another?
COUNTIFS only does "and". For "or", add two counts together: =COUNTIFS(A:A,"North")+COUNTIFS(A:A,"South"). This is safe as long as no single row can match both.
How do I count dates between two dates?
Use the date column twice, once for each end: =COUNTIFS(A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,3,31)).
Related functions
- COUNTIF — COUNTIF counts how many cells in a range match one condition.
- SUMIFS — SUMIFS adds up the numbers in one column, but only on the rows that satisfy every condition you give it.
