COUNTIF in Google Sheets
COUNTIF counts cells that match one condition. Use it for exact text, comparison operators, nonblank cells, or wildcard patterns—and use COUNTIFS when every row must satisfy multiple conditions.
Reviewed by the SheetsX team
The range is inspected once, and the criterion defines the single condition a cell must meet.
Counts the four task rows whose Status value is exactly Complete in the recorded demo.
SheetsX verified complete, blocked, four-or-more-hour, and nonblank-owner counts without changing the source data.
How do you use COUNTIF in Google Sheets?
Enter =COUNTIF(range, criterion) in an empty cell. For example, =COUNTIF(C2:C9,"Complete") counts cells equal to Complete, =COUNTIF(D2:D9,">=4") counts values of four or more, and =COUNTIF(B2:B9,"<>") counts nonblank cells. Put text and comparison criteria inside quotation marks, then verify the result against a visible sample.
Watch SheetsX write and verify four COUNTIF formulas
The Agent counts exact text, a numeric threshold, and nonblank cells in one task tracker. It replaces only the four incorrect summary values, updates their fill color, and confirms that all source data and labels remain unchanged.
The recording uses fictitious task data. It shows the exact English prompt, the Agent's progress, the calculated values 4, 2, 4, and 8, and the final no-other-cells-changed verification.
“Fix the incorrect summary in G2:G5. Replace the hardcoded zeros with these COUNTIF formulas and do not change any task data or labels: G2 =COUNTIF(C2:C9,"Complete"), G3 =COUNTIF(C2:C9,"Blocked"), G4 =COUNTIF(D2:D9,">=4"), and G5 =COUNTIF(B2:B9,"<>"). Remove the red fill from G2:G5, use a pale green fill, and verify the displayed results are 4, 2, 4, and 8. Reply in English and confirm that no other cells changed.”
How to use COUNTIF in Google Sheets
Define one condition, choose a range containing comparable values, and test the formula on data you can inspect before applying it to a larger sheet.
- 1
Choose the range to count
Select the cells that contain the values you want to test, such as C2:C9 for task status. Exclude the header unless it should count.
- 2
Write one criterion
Enter =COUNTIF(range, criterion). Put text, wildcards, and comparison expressions such as >=4 inside quotation marks.
- 3
Use COUNTIFS for multiple conditions
COUNTIF accepts one condition. If matching rows must satisfy two or more range-and-criterion pairs, switch to COUNTIFS.
- 4
Verify the displayed count
Compare the result with a small visible sample. Check for unexpected spaces, formulas that display blank text, and ranges with mixed data types.
COUNTIF formulas for common Google Sheets tasks
These examples cover the patterns used in the demo plus a wildcard search. Adjust the ranges and criteria to match your own sheet.
| Use case | Formula or setting | What it does |
|---|---|---|
| Count an exact status | =COUNTIF(C2:C9,"Complete") | Counts cells in C2:C9 whose value equals Complete. COUNTIF text matching is not case-sensitive. |
| Count another exact value | =COUNTIF(C2:C9,"Blocked") | Counts the two Blocked task rows in the recorded sample. |
| Count values at or above a threshold | =COUNTIF(D2:D9,">=4") | Counts numeric cells whose value is greater than or equal to four. Keep the operator and number together inside quotes. |
| Count nonblank cells | =COUNTIF(B2:B9,"<>") | Counts cells that are not empty. Inspect formulas returning an empty string if the result differs from what appears visually blank. |
| Count text containing a word | =COUNTIF(A2:A100,"*brief*") | Uses asterisks as wildcards to count cells containing brief anywhere in the text. |
Ask SheetsX to write COUNTIF and verify the result
A precise Agent prompt names the source range, criterion, destination cells, expected results, and everything that must remain unchanged. That makes the formula insertion and verification auditable.
Install SheetsX- Open a new English Agent chat for a clean task context.
- Name every source range and result cell explicitly.
- Provide the exact criterion or describe the business rule in plain language.
- Tell the Agent which data, labels, and formatting must stay unchanged.
- Review both the formulas and their displayed values before continuing.
COUNTIF questions
What is the COUNTIF syntax in Google Sheets?+
The syntax is =COUNTIF(range, criterion). The range contains the cells to inspect, and the criterion is the one condition each cell must meet to be counted.
How do I use COUNTIF with text?+
Put the text inside quotation marks, for example =COUNTIF(C2:C100,"Complete"). You can use * for any sequence of characters and ? for one character.
How do I count values greater than or equal to a number?+
Combine the comparison operator and number inside one quoted criterion, such as =COUNTIF(D2:D100,">=4").
Can COUNTIF use multiple criteria?+
COUNTIF evaluates one criterion. Use COUNTIFS when the same row must meet multiple range-and-criterion pairs, such as a Complete status and at least four hours.
Why does my COUNTIF result look wrong?+
Check the range boundaries, extra spaces, mixed text and numeric values, wildcard characters, and cells that look blank but contain a formula. Test the criterion against a small visible sample.
Can SheetsX create COUNTIF formulas without changing my source data?+
Yes. Name the destination cells and explicitly tell the Agent which source ranges, labels, and formatting must remain unchanged. The demo verifies all four results and reports that no other cells changed.