How to Sum Values in WPS Spreadsheets When a Related Cell Is TRUE
Question details
The user wants to replace multiple IF formulas with one formula that totals column L only when the matching cells in column O contain TRUE or an equivalent indicator.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a data range in columns L and O, where column O holds TRUE/FALSE values or numeric indicators, and they need a conditional sum of column L.
- Observed behavior
- The current worksheet uses a long series of IF formulas such as IF(O5,SUM(L5),0), and the user is looking for a single-formula alternative.
Confirm whether column O contains real Boolean TRUE/FALSE values or numeric indicators such as 1 and 0. Check that the sum range L5:L20 and condition range O5:O20 are the same size and contain no #N/A or #VALUE! errors in the condition cells.
Use a single SUM formula for Boolean TRUE/FALSE cells
Use this when column O already contains real Boolean TRUE or FALSE values and you want the shortest direct formula.
This formula multiplies each L value by its corresponding O value. In spreadsheet calculation, TRUE behaves as 1 and FALSE behaves as 0, so only L values paired with TRUE remain in the total.
Click the cell where you want the conditional sum to appear, for example P2.
Type =SUM(L5:L20*O5:O20) and press Enter in a dynamic-array version of WPS Spreadsheets. If you see a #VALUE! error, click the formula bar and press Ctrl+Shift+Enter to confirm it as an array formula.
Check that column O cells show TRUE/FALSE without quotes. If they are text, use the SUMIF solution below instead.
Use SUMIF when column O contains numeric indicators or text markers
Use this when the condition column uses numbers such as 1/0 or other non-zero indicators, or when you want broader compatibility.
Create a single conditional SUM formula in WPS Office Spreadsheets
Instead of maintaining many IF formulas, use WPS Office Spreadsheets to write one SUM or SUMIF formula that totals only the rows matching your TRUE condition, with full Excel formula compatibility.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains columns L and O, and click the sheet tab with your data.
- 2. Select the result cell: Click the cell where you want the conditional sum to appear.
- 3. Enter the appropriate formula: Use =SUM(L5:L20*O5:O20) for Boolean TRUE/FALSE cells, or =SUMIF(O5:O20,"<>0",L5:L20) for numeric condition cells.
- 4. Confirm the result: Press Enter, or Ctrl+Shift+Enter if required, and check that the returned total matches the intended TRUE rows.
Frequently Asked Questions
Can I sum column L when column O contains TRUE/FALSE text instead of Boolean values?
Yes. If the cells contain text "TRUE", use =SUMIF(O5:O20,"TRUE",L5:L20). For real Boolean values, use =SUMIF(O5:O20,TRUE,L5:L20).
Why does =SUM(L5:L20*O5:O20) return #VALUE! in WPS Spreadsheets?
This usually means the formula was entered as a normal formula in a version that requires array formula confirmation, or one of the condition cells contains text or errors. Click the formula bar and press Ctrl+Shift+Enter, or switch to the SUMIF version to avoid array entry.
What formula should I use if column O contains 1 and 0 instead of TRUE/FALSE?
Use =SUMIF(O5:O20,"<>0",L5:L20) to sum the values in L5:L20 where the matching O cell is non-zero.
Can I use SUMPRODUCT instead of the SUM multiplication formula?
Yes. =SUMPRODUCT(L5:L20*O5:O20) works as a non-array formula in many versions, but the same logic applies: TRUE evaluates as 1 and FALSE as 0.
