ExcelFlux

COUNTIFS function

COUNTIFS counts rows that meet every supplied criterion.

Statistical

Where can you use it?

Analytics, quality and research teams. Use this group to examine distributions, variability or relationships in measurements, sales or service durations.

Scenario in the workbook

Check region, status and amount on the same row. All criteria ranges must have the same dimensions.

A worked example from the workbook

RowABC
6RegionStatusAmount
7NorthPaid120
8SouthPaid75
9NorthOpen40
10NorthPaid80
11SouthOpen0
North / Paid
=COUNTIFS(A7:A11,"North",B7:B11,"Paid")

Expected result2

See the inputs and instructions for all 3 examples

Syntax

COUNTIFS(criteria_range1, criteria1, …)

Copyable formulas

English Excel · en-US
=COUNTIFS(A2:A5,D2,B2:B5,">="&D3)
Turkish Excel · tr-TR
=ÇOKEĞERSAY(A2:A5;D2;B2:B5;">="&D3)

Sample data

Enter the headers and sample records in A1:B5.

Team Hours
Warehouse 8
Field 9
Warehouse 6
Warehouse 7
  • D2 = Warehouse
  • D3 = 7

Enter the formula in E2.

To try the Turkish formula, use the sample data on the Turkish page.

Expected result

2

How it works

To build a criterion from a threshold cell, quote the operator and append the reference with &. The threshold includes exactly seven hours. Raise it to eight and the count becomes 1.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2], [criteria2], …)

Compatibility

Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.

Worked examples

Check region, status and amount on the same row. All criteria ranges must have the same dimensions.

Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.

Row A B C
6 Region Status Amount
7 North Paid 120
8 South Paid 75
9 North Open 40
10 North Paid 80
11 South Open 0
Case Formula Expected result
North / Paid =COUNTIFS(A7:A11,"North",B7:B11,"Paid") 2
South / Open =COUNTIFS(A7:A11,"South",B7:B11,"Open") 1
North / ≥ 100 =COUNTIFS(A7:A11,"North",C7:C11,">=100") 1
Microsoft Excel reference