Fix an Excel IFS Formula with AND Conditions
Question details
Build a formula that evaluates multiple conditions and returns the appropriate calculation or zero.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants to calculate a result based on whether E6 and D6 contain positive numbers or the text TM.
- Observed behavior
- The original formula has mismatched parentheses and places calculations inside logical functions rather than pairing each test with a return value.
Check that the CALCULATOR sheet and the referenced cells exist. Confirm whether the final calculation should use K15 on the current sheet, rather than K15 on the CALCULATOR sheet.
Correct the IFS formula and separate tests from calculations
Use IFS to evaluate each condition in order and IFERROR to return zero when a calculation fails or no condition matches.
IFS expects a logical test followed by the value to return. AND combines tests; it should not contain the calculation being returned.
ISNUMBER ensures that the positive-number tests do not accidentally match text such as TM. Numeric values stored as text will not pass those tests.
In Excel, select the cell containing the incorrect formula and click the formula bar.
Replace the formula with =IFERROR(IFS(AND(ISNUMBER(E$6),E$6>0),B7*E$6,AND(E$6="TM",ISNUMBER($D$6),$D$6>0),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15,AND(E$6="TM",$D$6="TM"),CALCULATOR!$I18*CALCULATOR!$M15/$K15),0) and press Enter.
Test the formula with a positive number in E6, then with TM in E6 and either a positive number or TM in D6. Check the referenced K15 values if a calculation returns zero unexpectedly.

Use nested IF when IFS is unavailable
Use this alternative if your spreadsheet version does not support IFS.
Build the conditional formula in WPS Spreadsheets
Use nested IF, AND, and IFERROR in WPS Spreadsheets to apply the same conditional calculations to your workbook.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Select the result cell: Click the cell where the conditional calculation should appear, then click the formula bar.
- 3. Enter the formula: Paste =IFERROR(IF(AND(ISNUMBER(E$6),E$6>0),B7*E$6,IF(AND(E$6="TM",ISNUMBER($D$6),$D$6>0),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15,IF(AND(E$6="TM",$D$6="TM"),CALCULATOR!$I18*CALCULATOR!$M15/$K15,0))),0) and press Enter.
- 4. Test and save: Check the results for the expected E6 and D6 values, then use File > Save to save the workbook.

Frequently Asked Questions
Why does IFS need a value after each condition?
IFS uses test-and-result pairs: IFS(test1, result1, test2, result2). Put calculations in the result positions, not inside AND.
Why does the formula use ISNUMBER before checking for a positive value?
ISNUMBER restricts the positive-value branch to actual numbers so text such as TM does not match it. A number stored as text will not pass this check.
Why does the formula return zero when I expect a calculation?
No condition may have matched, or IFERROR may have caught an error such as division by zero. Check E6, D6, and the relevant K15 divisor; you can temporarily remove IFERROR to see the underlying error.
What is the difference between CALCULATOR!$K15 and $K15?
CALCULATOR!$K15 refers to column K on the CALCULATOR sheet. $K15 without a sheet name refers to column K on the sheet containing the formula. In both references, the column is fixed and the row can change when the formula is copied.
Can I use this formula without IFS?
Yes. Use the nested IF alternative above; it evaluates the same conditions in order and returns zero when none match or an error occurs.




