Count Visible Values Greater Than Zero in WPS Spreadsheets
Question details
Count numeric values greater than zero in the visible rows of a filtered worksheet.

- 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.
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.
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.
Click an empty cell outside the data range, such as D11 when the values are in D2:D9.
Enter =SUMPRODUCT(SUBTOTAL(103,OFFSET(D2,ROW(D2:D9)-ROW(D2),0,1)),--ISNUMBER(D2:D9),--(D2:D9>0)) and press Enter.
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 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. Open the worksheet: Open WPS Spreadsheets and use File > Open to select your workbook.
- 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. 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. Save the workbook: Choose File > Save or Save As to keep the formula in your workbook.

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.




