Office Dễ Hiểu

How to count matching cells with COUNTIF in Excel

Count submitted records using a text condition.

COUNTIF counts cells that meet one condition. This example counts cells containing Submitted in B2:B5.

Visual walkthrough

ExcelE2COUNTIF

Before

Four records include two Submitted statuses.

After

COUNTIF returns 2.

Check the result

Change HS02 to Submitted. The count should become 3.

Try it here

Change the selection to see the result and matching data rows.

=COUNTIF(B2:B5,E2)
Record IDStatus
HS01Submitted
HS02Awaiting details
HS03Submitted
HS04Not submitted
Result2
Record (A)Status (B)
HS01Submitted
HS02Awaiting details
HS03Submitted
HS04Not submitted

If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.

Step-by-step instructions

  1. Enter the sample record IDs in column A and statuses in column B.
  2. Enter the formula below in an empty cell.
    =COUNTIF(B2:B5,"Submitted")
  3. The result should be 2 because HS01 and HS03 are Submitted.
  4. Change HS02 to Submitted and confirm that the result becomes 3. Use a reference such as E2 instead of the quoted text to select the condition from a cell.

Frequently asked questions

How do I count using two conditions?

Use COUNTIFS, with a range and criterion pair for each condition. The criteria ranges must have matching dimensions.

Reference: Microsoft Support