FormulaGym
XP 0
Streak 0

The COUNTIFS function in Excel

COUNTIFS counts the rows that meet every one of several conditions.

What COUNTIFS does

Each condition is a pair: the cells to check and the test to apply. A row is counted only when it passes all the tests. All the ranges must be the same size.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
criteria_range1
The first set of cells to test.
criteria1
The first condition, such as "North".
criteria_range2, criteria2
Optional. More pairs. A row must pass every pair to be counted.

Worked example

ABC
1ProductRegionSales
2LampNorth120
3MugSouth80
4DeskNorth150
5ChairEast90
6PlantNorth60
7North over 1002
C7 =COUNTIFS(B2:B6,"North",C2:C6,">100")

Three rows are North, but only two of them have sales above 100.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

HR

Count full-time staff in one department.

Sales & Ops

Count orders over a value in one region.

Data cleanup

Find rows where a value repeats across two columns.

Common mistakes

  • Ranges of different sizes. COUNTIFS returns a #VALUE! error if they do not match.
  • Expecting OR logic. COUNTIFS needs every condition to be true. To count either of two conditions, add two COUNTIFS together.
  • Leaving the quotes off a comparison such as ">100".

The COUNTIFS course

  1. 1
    Count with two conditionsFree practice below
    Practise
  2. 2
    Count between two valuesComing soon
  3. 3
    Count by team and statusComing soon
  4. 4
    Spot duplicates across columnsComing soon
  5. 5
    Dashboard countsComing soon

Common questions

Can COUNTIFS count rows that match either condition?

Not directly. Add two COUNTIFS together and subtract any rows that match both, so they are not counted twice.

How many conditions can COUNTIFS take?

Up to 127 pairs of range and criteria.

Related functions