Formula Errors

Fix an Excel IFS Formula with AND Conditions

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Build a formula that evaluates multiple conditions and returns the appropriate calculation or zero.

Fix an Excel IFS Formula with AND Conditions
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, select the cell containing the incorrect formula and click the formula bar.

2
Enter the corrected formula

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.

3
Check the results

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.

Correct the IFS formula and separate tests from calculations
Zero can hide errors: IFERROR also returns zero for errors such as division by zero. The second calculation uses CALCULATOR!$K15, while the third uses $K15 on the current sheet; change the reference if that distinction is not intended.
Work with formulas in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Select the result cell: Click the cell where the conditional calculation should appear, then click the formula bar.
  3. 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. 4. Test and save: Check the results for the expected E6 and D6 values, then use File > Save to save the workbook.
Evaluate multiple conditions with nested IF and ANDReturn zero for unmatched conditions or calculation errorsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.