Calculation Issues

Count Visible Values Greater Than Zero in WPS Spreadsheets

Muhammad TalhaMuhammad Talha Nov 20, 2026 868 views

Question details

Count numeric values greater than zero in the visible rows of a filtered worksheet.

Count Visible Values Greater Than Zero in WPS Spreadsheets
Product
WPS Spreadsheets or Microsoft Excel
Device & OS
not provided
Scenario
A user wants a count that updates when worksheet rows are filtered or hidden.
Observed behavior
An ordinary COUNTIF formula also counts values in hidden rows.
Before you start

Identify the single-column data range to count and choose an empty result cell outside that range. Apply a filter from the Data tab if you want to test the count against filtered rows.

Solution 1Recommended

Count visible positive numbers with SUBTOTAL and SUMPRODUCT

Use this formula when the count should exclude both filtered rows and manually hidden rows.

SUBTOTAL with function number 103 checks whether each nonempty cell is visible. SUMPRODUCT combines that visibility check with tests for numeric values greater than zero.

1
Select the result cell

Click an empty cell outside the data range, such as D11 when the values are in D2:D9.

2
Enter the formula

Enter =SUMPRODUCT(SUBTOTAL(103,OFFSET(D2,ROW(D2:D9)-ROW(D2),0,1)),--ISNUMBER(D2:D9),--(D2:D9>0)) and press Enter.

3
Adjust and test the range

Replace D2:D9 and the starting-cell references D2 with your actual single-column range and its first cell. Use Data > Filter or hide a row to check that the result updates.

Count visible positive numbers with SUBTOTAL and SUMPRODUCT
Values included in the count: The formula counts visible numeric values above zero, not numbers stored as text. Errors in the range can cause the formula to return an error.
WPS Spreadsheets

Count filtered positive values in WPS Spreadsheets

Use a SUBTOTAL and SUMPRODUCT formula in WPS Spreadsheets to count positive numeric values only in visible rows.

  1. 1. Open the worksheet: Open WPS Spreadsheets and use File > Open to select your workbook.
  2. 2. Apply a filter: Select your data and choose Data > Filter, then use a column's filter arrow to show the rows you need.
  3. 3. Enter the count formula: Click an empty cell and enter =SUMPRODUCT(SUBTOTAL(103,OFFSET(D2,ROW(D2:D9)-ROW(D2),0,1)),--ISNUMBER(D2:D9),--(D2:D9>0)). Replace the references with your data range and press Enter.
  4. 4. Save the workbook: Choose File > Save or Save As to keep the formula in your workbook.
Update the count as rows are filtered or manually hiddenWork with Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filtering tools
microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIF include filtered-out rows?

COUNTIF evaluates the cells in its range without checking whether their rows are visible. Combine SUBTOTAL with SUMPRODUCT to apply a visibility check.

Does the formula exclude manually hidden rows?

Yes. SUBTOTAL function number 103 excludes manually hidden rows as well as filtered-out rows. Function number 3 excludes filtered-out rows but includes manually hidden rows.

Will zero, negative numbers, or text be counted?

No. The formula uses ISNUMBER and a greater-than-zero test, so it counts only visible numeric values above zero. Numbers stored as text are also excluded.

How do I change the formula for another column?

Replace D2:D9 with your single-column data range and replace each standalone D2 with the first cell of that range. Keep the result cell outside the range.