Conditional formula page
COUNTIF Formula
Use COUNTIF when one column decides whether a row should be counted.
Count matching rows with COUNTIF.
With the sample data, this counts completed tasks.
Copy formulas
=COUNTIF(C2:C100, "Complete")=COUNTIF(C2:C100, "Complete")Excel and Google Sheets use the same formula syntax for this pattern.
Example data
| Task | Owner | Status | Due Date | Hours |
|---|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 | 3 |
| Clean headers | Nico | In Progress | 2026-01-08 | 2 |
| Review budget | Maya | Complete | 2026-01-12 | 4 |
| Publish report | Iris | Blocked | 2026-01-15 | 1 |
Formula explanation
- C2:C100 is the range being checked.
- Complete is the criteria.
- The result is a count, not a total amount.
What this formula does
COUNTIF Formula is built around COUNTIF. The example formula on this page is =COUNTIF(C2:C100, "Complete"), and it is written against sample columns such as Task, Owner, Status, Due Date, Hours.
Excel and Google Sheets use the same copy pattern here, so the main work is adjusting ranges, criteria, and output cells to match your sheet.
Criteria formulas test each row before returning a total, count, or average. Every criteria range should cover the same row numbers as the result range so row-level logic stays aligned.
Use this pattern for this task: Count matching rows with COUNTIF. Date and text criteria should be real spreadsheet values, not display-only text that only looks correct on screen.
| Syntax piece | Role in the formula |
|---|---|
| C2:C100 | C2:C100 is the range being checked. |
| Part 2 | Complete is the criteria. |
| Part 3 | The result is a count, not a total amount. |
Practical use cases
=COUNTIF(C2:C100, "Complete")Excel: Use this in a sales, task, or reporting table where each row is tested before the final number is returned. Returns: With the sample data, this counts completed tasks.
=COUNTIF(C2:C100, "Complete")Google Sheets: Use this for dashboards where changing a criterion cell should refresh the result without rebuilding a pivot table. Returns: With the sample data, this counts completed tasks.
Common errors
- Criteria ranges must cover the same rows as the sum, count, or average range.
- Text criteria need quotes when typed manually.
- Date criteria should compare real date values, not month names typed as text.
Common errors and troubleshooting
| Issue | Likely cause | Fix |
|---|---|---|
| Copied formula gives an unexpected result | Criteria ranges must cover the same rows as the sum, count, or average range. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Text criteria need quotes when typed manually. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Common setup problem | Date criteria should compare real date values, not month names typed as text. | Resize the criteria ranges to the same rows, quote text criteria, and compare dates as real spreadsheet dates. |
| Formula returns 0 | The criteria text, date value, or numeric comparison does not match the source cells exactly. | Test the criterion against one row, then confirm all criteria ranges start and end on the same rows. |
| #VALUE! from criteria ranges | One criteria range is a different shape from the sum, count, or average range. | Use matching row counts such as A2:A100 with D2:D100 instead of mixing whole-column and partial-column references. |
When not to use this formula
- Do not use criteria formulas to return matching rows; use FILTER or QUERY for row output.
- Do not use SUMIF or COUNTIF when you need several independent conditions; use the IFS version instead.
Alternatives
| Alternative | When to use it |
|---|---|
| SUMIFS Formula Builder | Use when the result should be a total amount. |
| COUNTIFS Formula Builder | Use when the result should be a row count. |
| SUMIFS vs COUNTIFS Guide | Use this guide when you are choosing between totals and counts. |
Compare with related formulas
| Formula or tool | How it differs | Use it instead when |
|---|---|---|
| SUMIFS Formula Builder | SUMIFS Formula Builder solves a nearby workflow, while COUNTIF Formula is focused on count matching rows with countif. | Use when the result should be a total amount. |
| COUNTIFS Formula Builder | COUNTIFS Formula Builder solves a nearby workflow, while COUNTIF Formula is focused on count matching rows with countif. | Use when the result should be a row count. |
| SUMIFS vs COUNTIFS Guide | SUMIFS vs COUNTIFS Guide solves a nearby workflow, while COUNTIF Formula is focused on count matching rows with countif. | Use this guide when you are choosing between totals and counts. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =COUNTIF(C2:C100, "Complete") | With the sample data, this counts completed tasks. |
| Google Sheets copy output | Google Sheets | =COUNTIF(C2:C100, "Complete") | With the sample data, this counts completed tasks. |
Related formulas
FAQ
What does the COUNTIF Formula do?
It supports this task: Count matching rows with COUNTIF. The example uses C2:C100, but you should replace the ranges and criteria with the cells in your own workbook.
Does the COUNTIF Formula work in Excel and Google Sheets?
Yes. The Excel and Google Sheets versions on this page use the same syntax for this pattern.
What should I change before copying the formula?
Change the source ranges, criteria cells, and output range references so they match your sheet. Keep related ranges on the same rows unless the formula notes say otherwise.
Why does this criteria formula return zero?
The criteria may not match the source cells exactly, or the criteria ranges may not align with the result range. Test one row and then confirm the full ranges.
Can I add another condition?
Use the IFS version of the formula when you need multiple independent criteria, such as region plus product plus date.
How should I handle dates in criteria formulas?
Use real date cells or date functions such as DATE and EOMONTH. Month names typed as text are easier to break.