Function Problems

How to Count Nonzero and Nonblank Cells in Excel

Bushra Parveen Oct 10, 2026 870 views

Question details

The user needs a formula to accurately count the number of cells in a specific Excel range that are neither empty nor contain the value zero.

How to Count Nonzero and Nonblank Cells in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing data analysis where empty cells and zero-value data points must be excluded from the total count.
Observed behavior: Requires a reliable method to evaluate a dataset (such as row A1:F1) and return the count of active, non-zero entries.

Before you start

Ensure your data range does not contain hidden characters or spaces, as these may be counted as non-blank text by Excel functions.

Solution 1Recommended

Use the COUNTIF Function (Recommended)

The most straightforward way to count cells that are not equal to zero while automatically excluding blank cells.

The COUNTIF function evaluates a range based on a single condition. By using the 'not equal to' operator, you can filter out zero values in a single step.

Select the result cell

Click on an empty cell where you want the final count to appear.

Input the formula

Type the formula =COUNTIF(A1:F1,"<>0") into the formula bar. Adjust the range A1:F1 to match the location of your actual data.

Apply the formula

Press the Enter key to calculate the result. The cell will now display the total count of non-zero and non-empty cells.

Exclude Blanks Automatically: By default, the "<>0" criteria in the COUNTIF function automatically excludes truly empty cells from being counted.

Solution 2

Combine COUNTA and COUNTIF Functions

An alternative method that explicitly counts all non-blank cells first, then subtracts the cells containing exactly zero.

Efficient Data Analysis

Count Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions like COUNTIF and COUNTA, allowing you to accurately calculate non-zero and non-blank cells with a familiar and intuitive interface.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Select a target cell: Click the cell where you want to display the calculation result.
3. Access the function library: Navigate to the 'Formulas' tab on the top ribbon and select 'Insert Function'.
4. Configure COUNTIF: Search for COUNTIF, select your target data range, and input "<>0" in the criteria box.
5. View the result: Click 'OK' or press Enter to apply the formula and view your accurate count.

Fully compatible with Microsoft Excel formulas and functionsLightweight software for fast data processing without lagBuilt-in function helper to easily guide formula creation

Frequently Asked Questions

Does COUNTIF("<>0") count cells with spaces as non-zero?

Yes, if a cell contains a space character, the spreadsheet treats it as a text string, which is technically not equal to zero. To avoid counting spaces, you may need to clean your data first using the TRIM function or Find and Replace.

How do I count cells strictly greater than zero?

To count only positive numbers while excluding zeros, blanks, and negative numbers, use the COUNTIF function with the greater-than criteria. For example, enter the formula =COUNTIF(A1:F1, ">0").

Why is the COUNTA function counting cells that look blank?

The COUNTA function counts any cell that is not technically empty. If a cell contains a formula that returns an empty string ("") or hidden formatting characters like an apostrophe, COUNTA will include it in the final count.

Excel & Data Problems