ExcelFlux

COUNTIF function

COUNTIF returns the number of cells matching one 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

Count open records. Put comparison operators inside quotes.

A worked example from the workbook

RowABC
6RegionStatusAmount
7NorthPaid120
8SouthPaid75
9NorthOpen40
10NorthPaid80
11SouthOpen0
Open
=COUNTIF(B7:B11,"Open")

Expected result2

See the inputs and instructions for all 3 examples

Syntax

COUNTIF(range, criteria)

Copyable formulas

English Excel · en-US
=COUNTIF(B2:B5,C2)
Turkish Excel · tr-TR
=EĞERSAY(B2:B5;C2)

Sample data

Enter the headers and sample records in A1:B5.

Task Status
Count Open
Delivery Closed
Report Open
Review
  • C2 = Open

Enter the formula in D2.

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

Expected result

2

How it works

The range is B2:B5. The blank status does not match Open. Change C2 to Closed and the result is 1. Use COUNTIFS for two separate conditions.

Syntax

COUNTIF(range, criteria)

Compatibility

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

Worked examples

Count open records. Put comparison operators inside quotes.

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
Open =COUNTIF(B7:B11,"Open") 2
Paid =COUNTIF(B7:B11,"Paid") 3
Amount ≥ 80 =COUNTIF(C7:C11,">=80") 2
Microsoft Excel reference