COUNTIF counts cells that meet one condition. This example counts cells containing Submitted in B2:B5.
Visual walkthrough
Before
Four records include two Submitted statuses.
After
COUNTIF returns 2.
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 ID | Status |
|---|---|
| HS01 | Submitted |
| HS02 | Awaiting details |
| HS03 | Submitted |
| HS04 | Not submitted |
Sample data
Download sample CSV| Record (A) | Status (B) |
|---|---|
| HS01 | Submitted |
| HS02 | Awaiting details |
| HS03 | Submitted |
| HS04 | Not submitted |
If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.
Step-by-step instructions
- Enter the sample record IDs in column A and statuses in column B.
- Enter the formula below in an empty cell.
=COUNTIF(B2:B5,"Submitted") - The result should be 2 because HS01 and HS03 are Submitted.
- 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