Count Visible Nonblank Cells While Ignoring Formula Blanks in WPS Spreadsheets
Question details
User needs to count only visible cells with real content in a filtered WPS Spreadsheets range, excluding cells where formulas return empty strings.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a filtered or hidden-row list containing formulas that display blank text and wants an accurate visible-cell count.
- Observed behavior
- SUBTOTAL with function number 103 counts visible nonblank cells, but formulas that return "" are not technically empty and may still be counted.
Open the workbook in WPS Spreadsheets, confirm which range contains the formula-blank cells, and note whether rows are hidden by a filter or manually before you apply the formula.
Use SUMPRODUCT, SUBTOTAL, and LEN to Count Only Visible Cells with Real Content
Use this formula when you need a single-cell result that ignores formula blanks and automatically respects filtered or hidden rows.
SUBTOTAL(103) alone checks visibility but treats empty strings as nonblank. Adding LEN(range)>0 forces the formula to count only cells that contain at least one character, so formula blanks and truly empty cells are ignored.
Click the cell in WPS Spreadsheets where you want the visible-cell count to appear.
Type =SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))*(LEN(A2:A100)>0)) and adjust A2:A100 to match your range, then press Enter.
Apply or change a filter and confirm that the total updates to count only visible rows whose cells contain actual text or numbers.
Add a Helper Column to Flag Real Content and Count Visible Flags
Use this method when you can add a helper column and want a more transparent audit trail for the counted rows.
Count Visible Nonblank Cells in WPS Spreadsheets Without Counting Formula Blanks
Open your workbook in WPS Spreadsheets and use a SUMPRODUCT formula that combines SUBTOTAL visibility checks with LEN-based content checks. It counts only visible rows that contain real text or numbers and skips formulas that display as blank.
- 1. Open the workbook: Launch WPS Office and open the .xlsx file in WPS Spreadsheets.
- 2. Select the result cell: Click the cell where you want the visible-cell count result to appear.
- 3. Enter the formula: Type =SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))*(LEN(A2:A100)>0)) and press Enter, replacing A2:A100 with your actual range.
- 4. Check filtered rows: Apply a filter to the range and verify that the total updates to count only visible cells with actual content.
- 5. Save the workbook: Save the file in .xlsx format so it remains compatible with Microsoft Excel.
Frequently Asked Questions
Why does SUBTOTAL(103) count cells that look blank?
Because a formula that returns "" is not truly empty; it contains an empty text string. SUBTOTAL(103) sees it as nonblank. Use LEN(A2:A100)>0 to exclude empty-string results.
Can AGGREGATE ignore formula blanks automatically?
AGGREGATE can ignore cells containing SUBTOTAL or AGGREGATE formulas, but it does not ignore every formula that returns an empty string. For reliable results, combine a visibility check with LEN or use a helper column.
Will this formula update when I change filters in WPS Spreadsheets?
Yes. SUBTOTAL recalculates after filtering, and the SUMPRODUCT formula returns the count for visible rows with actual content only.
Do I need to press Ctrl+Shift+Enter for this formula in WPS?
In WPS Spreadsheets, SUMPRODUCT normally works as a regular formula. If an older version prompts for array entry, press Ctrl+Shift+Enter to confirm.
Can I use this formula on Microsoft Excel files?
Yes. WPS Spreadsheets opens .xlsx files and supports the same formula syntax, so you can use this calculation and save the workbook for Excel compatibility.
