Use IF Criteria Across Multiple Cells and Sum Matching Rows in WPS Office Spreadsheets
Question details
The user needs two formulas in WPS Office Spreadsheets: one to return a word when it appears in a specific cell but not in another range, and another to sum values in column F when the row's column A value appears anywhere in columns B through D without double counting.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a table where column A holds names, columns B:D may also contain names, and column F holds values to sum based on matching rows.
- Observed behavior
- The first formula should output a value only when the text appears in one cell and not in the nearby range. The second formula should total column F once per row when the name in column A appears anywhere in B:D.
Confirm that your data ranges are aligned, such as B1:B1000 and F1:F1000 having the same number of rows, and that column F contains numeric values for the SUMPRODUCT sum to calculate correctly.
Return a Word When It Appears in One Cell but Not in a Range
Use this formula when you want to output a text value only if a specific cell contains it and a neighboring range does not.
The formula combines SEARCH to detect "Jess" inside A1 and COUNTIF to check whether the exact word appears in B1:D1. The AND function makes the result appear only when both conditions are true.
Click the cell where you want the result, such as E1, in your WPS Office Spreadsheets worksheet.
Type =IF(AND(ISNUMBER(SEARCH("Jess",A1)),COUNTIF(B1:D1,"Jess")=0),"Jess","") and press Enter.
If A1 contains "Jess" and B1:D1 does not contain an exact "Jess" entry, the cell returns "Jess"; otherwise it returns an empty string.
Sum Column F for Rows Where the Column A Value Appears in Columns B:D
Use this formula to total all values in F where the name in column A appears in any of columns B, C, or D on the same row, and count each row only once.
Create IF and SUMPRODUCT Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets can handle both formulas directly, using the same syntax as Excel for text criteria and conditional sums, so you can keep working in a free, compatible spreadsheet app.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook that contains the columns A through F data.
- 2. Select the result cell: Click the worksheet cell where the formula result should appear, such as E1 for the text check or E7 for the total.
- 3. Type the formula: Enter the IF formula or the SUMPRODUCT formula from the solutions section exactly, then press Enter.
- 4. Save in .xlsx format: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the file to maintain compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the first formula use SEARCH instead of a simple A1="Jess" comparison?
SEARCH checks whether "Jess" appears anywhere within the cell text, not only when the entire cell equals "Jess". If you need the cell to equal exactly "Jess", use A1="Jess" instead.
Why does the SUMPRODUCT formula add B, C, and D comparisons?
Adding the comparisons creates a row-level OR-like count. The >0 test then converts values of 1, 2, or 3 to 1, so each row is included at most once even if the name appears in multiple B:D columns.
What should I check if the SUMPRODUCT formula returns #VALUE!?
Check that F1:F1000 contains numbers and that all referenced ranges have the same number of rows. Text or error values in the sum range can produce an error when multiplied inside SUMPRODUCT.
Can I use these formulas in Microsoft Excel after creating them in WPS Office?
Yes. These formulas are Excel-compatible and the workbook can be saved as .xlsx, so the formulas work in both WPS Office Spreadsheets and Microsoft Excel.
