How to Return 1 If Any Cell Contains Data in WPS Office Excel
Question details
The user needs a formula in cell J4 that returns 1 when any cell in the range F52:M52 contains data and remains blank when the entire range is empty.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating a row-level indicator in a WPS spreadsheet and needs cell J4 to display 1 when any cell in F52:M52 is populated.
- Observed behavior
- J4 should display 1 when any cell in F52:M52 contains data and remain blank when all cells in the range are empty.
Open your workbook in WPS Office Spreadsheets and click cell J4, where the result will appear. Confirm the target range is F52:M52 and note whether any cells contain formulas that return an empty string.
Use SUMPRODUCT to Detect Any Non-Empty Cell in F52:M52
Best for all WPS Office and Excel versions because it works without array entry and returns 1 when at least one cell is not empty.
The formula uses F52:M52<>"" to return TRUE or FALSE for each cell. The double negative -- converts those logical values to 1 or 0, and SUMPRODUCT adds them. The IF function then returns 1 when the total is greater than 0 and an empty text string otherwise.
In WPS Office Spreadsheets, open your worksheet and click cell J4 to make it the active cell.
Type =IF(SUMPRODUCT(--(F52:M52<>""))>0,1,"") in the formula bar, then press Enter.
Enter test data in any cell in F52:M52 and confirm J4 changes to 1. Delete the test data and confirm J4 returns blank.
Use COUNTA as a Simpler Formula in Current Spreadsheet Versions
A shorter alternative that counts non-empty cells; ideal if your range contains only manually entered values or you are using WPS Office, Microsoft 365, or Excel 2021 and newer.
Return a 1 in WPS Office Spreadsheets When Any Cell in a Row Range Contains Data
Use an IF and SUMPRODUCT formula in WPS Office Spreadsheets to flag a non-empty row range. The same workbook can be saved as .xlsx, so the formula remains compatible with Microsoft Excel.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the F52:M52 range.
- 2. Select the result cell: Click cell J4, or the cell where you want the 1/blank indicator to appear.
- 3. Enter the formula: Type =IF(SUMPRODUCT(--(F52:M52<>""))>0,1,"") and press Enter.
- 4. Confirm the behavior: Add test values to any cell in F52:M52 to see 1. Clear the range to see J4 return blank.
- 5. Save or share your workbook: Save the file as Workbooks (*.xlsx) to keep compatibility with WPS Office and Microsoft Excel.
Frequently Asked Questions
Can I change the formula to report 1 for a different row or range?
Yes. Click the cell with the formula, then edit F52:M52 to your target range, for example A2:Z2. The same IF and SUMPRODUCT structure works for any single row or column range.
Why does J4 show 1 when a cell looks blank?
If a cell contains a formula returning an empty string such as ="", the cell is not truly empty. Clear the cell with Delete to remove the formula result, or use a condition based on LEN and TRIM if you need to ignore empty-string results.
Does this formula actually count cells?
SUMPRODUCT(--(F52:M52<>"")) internally converts TRUE/FALSE checks to 1/0 and adds them, but the outer IF does not display a count. It only returns 1 when at least one cell is non-empty and blank when the range is empty.
Which formula should I use in WPS Office?
Use the SUMPRODUCT formula in WPS Office for broad compatibility. If your version supports Microsoft 365-style formulas, you can also use =IF(COUNTA(F52:M52)>0,1,"").
