Function Problems

WPS Spreadsheet COUNTIF: Ignore Blank Cells and Zero Values

Camila Milosovich Nov 12, 2026 869 views

Question details

The user needs a formula that counts repeated product codes without displaying results for empty rows and can optionally exclude zero values.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A product-code list uses COUNTIF to identify repeated codes, but empty rows should remain blank and unwanted zero values should not be counted.
Observed behavior: The formula needs an outer IF condition to return a blank for empty product-code rows and COUNTIFS criteria to exclude zero values when required.

Before you start

Confirm the product-code column and the full data range, such as Main!$E$3:$E$158. Decide whether 0 is an invalid value or a legitimate product code before adding the exclusion criterion.

Solution 1Recommended

Wrap COUNTIF in IF to hide results for blank rows

Use this formula when empty product-code rows should display nothing while repeated nonblank codes are counted.

The outer IF checks whether the current product-code cell is empty. If it is empty, the formula returns an empty string; otherwise, COUNTIF counts matching codes in the specified range.

Select the result cell

In WPS Spreadsheet, click the first cell where the duplicate or repeated-code count should appear, such as the row corresponding to Main!E3.

Enter the blank-safe formula

Type =IF(Main!E3="","",COUNTIF(Main!$E$3:$E$158,Main!E3)) in the formula bar or directly in the cell, then press Enter.

Fill the formula down

Select the completed formula cell and drag its fill handle down the result column, or use Home > Fill > Down to apply it to the remaining product-code rows.

Expected result: Rows with no product code remain blank, while nonblank rows show the number of matching product codes in Main!$E$3:$E$158.

Solution 2

Use COUNTIFS to exclude zero values from the count

Use this variation when 0 should not be counted as a valid product-code value.

Count Product Codes While Hiding Blank Rows in WPS Spreadsheet

WPS Spreadsheet lets you combine IF, COUNTIF, and COUNTIFS directly in the formula bar to count repeated product codes, leave empty rows blank, and exclude zero values when needed.

1. Open the spreadsheet: Launch WPS Office and open the workbook containing the product-code list.
2. Select the output cell: Click the first cell where the repeated-code count should appear.
3. Enter the formula: Use =IF(Main!E3="","",COUNTIF(Main!$E$3:$E$158,Main!E3)) for blank-safe counting, or use the COUNTIFS version to exclude zero.
4. Fill the formula down: Drag the fill handle down the result column or select Home > Fill > Down.

Create blank-safe COUNTIF and COUNTIFS formulas without helper columns.Exclude zero values with a clear not-equal-to-zero criterion.Fill formulas down while preserving fixed data ranges and row references.Open and save Microsoft Excel XLSX files with compatible spreadsheet formulas.

microsoft office alternative - wps office

Frequently Asked Questions

How do I keep COUNTIF blank when the product-code cell is empty?

Wrap COUNTIF in IF: =IF(Main!E3="","",COUNTIF(Main!$E$3:$E$158,Main!E3)). The formula returns an empty string when Main!E3 has no value.

How can I exclude zero from a COUNTIF result in WPS Spreadsheet?

Use COUNTIFS with a second criterion: =IF(Main!E3="","",COUNTIFS(Main!$E$3:$E$158,Main!E3,Main!$E$3:$E$158,"<>0")).

What does the dollar sign do in the product-code formula?

The dollar signs make Main!$E$3:$E$158 an absolute range, so the counted range stays fixed when you fill the formula down.

Should I use COUNTIF or COUNTIFS when zero is a valid product code?

Use the COUNTIF version if 0 should be counted as a legitimate code. Use COUNTIFS with "<>0" only when zero represents an unwanted value.

Excel & Data Problems