Conditional formula page
COUNTIF Nonblank Formula
Use COUNTIF with <> when you need to count filled cells in one range.
Count nonblank cells with COUNTIF.
The formula counts cells in A2:A100 that are not blank.
Copy formulas
=COUNTIF(A2:A100, "<>")=COUNTIF(A2:A100, "<>")Excel and Google Sheets use the same formula syntax for this pattern.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
Formula explanation
- A2:A100 is the range being checked.
- The <> criteria means not equal to blank.
- This is useful before cleanup or import checks.
What this formula does
COUNTIF Nonblank Formula is built around COUNTIF. The example formula on this page is =COUNTIF(A2:A100, "<>"), and it is written against sample columns such as Email, Region, Status.
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 nonblank cells 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 |
|---|---|
| A2:A100 | A2:A100 is the range being checked. |
| Part 2 | The <> criteria means not equal to blank. |
| Part 3 | This is useful before cleanup or import checks. |
Practical use cases
=COUNTIF(A2:A100, "<>")Excel: Use this in a sales, task, or reporting table where each row is tested before the final number is returned. Returns: The formula counts cells in A2:A100 that are not blank.
=COUNTIF(A2:A100, "<>")Google Sheets: Use this for dashboards where changing a criterion cell should refresh the result without rebuilding a pivot table. Returns: The formula counts cells in A2:A100 that are not blank.
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 Nonblank Formula is focused on count nonblank cells with countif. | Use when the result should be a total amount. |
| COUNTIFS Formula Builder | COUNTIFS Formula Builder solves a nearby workflow, while COUNTIF Nonblank Formula is focused on count nonblank cells 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 Nonblank Formula is focused on count nonblank cells with countif. | Use this guide when you are choosing between totals and counts. |
Test cases
| Test | Platform | Formula | Returns |
|---|---|---|---|
| Excel copy output | Excel | =COUNTIF(A2:A100, "<>") | The formula counts cells in A2:A100 that are not blank. |
| Google Sheets copy output | Google Sheets | =COUNTIF(A2:A100, "<>") | The formula counts cells in A2:A100 that are not blank. |
Related formulas
FAQ
What does the COUNTIF Nonblank Formula do?
It supports this task: Count nonblank cells with COUNTIF. The example uses A2:A100, but you should replace the ranges and criteria with the cells in your own workbook.
Does the COUNTIF Nonblank 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.