Fix Excel IF and AND Formulas Using Running Totals in WPS Spreadsheets
Question details
The user needs to correct nested IF and AND formulas so a total of 446 is categorized using the 350-to-700 range, either as a running total or a specified range total.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a category formula with IF, AND, and SUM and expects a total of 446 to fall into the 350-to-700 range.
- Observed behavior
- The formula does not return the expected category for the 350-to-700 range.
Verify the data range that should be totaled and decide whether the 350 and 700 limits are inclusive or exclusive. In this example, the data is in A1:A6 and the running total starts at A1.
Use a Fixed Range Total IF and AND Formula
Use this solution when you need to categorize the total of a static range such as A1:A6, including the 350-to-700 range.
A fixed range total evaluates SUM(A1:A6) once. Nested IF conditions then route the result to the correct category without depending on partial row totals.
In WPS Spreadsheets, click the cell where the result should appear, such as B6.
Type =IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<350),"A",IF(SUM(A1:A6)<700,"A","B")) and press Enter.
If 350 and 700 are inclusive, change the formula to =IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<=350),"A",IF(SUM(A1:A6)<=700,"A","B")).
Create a Running Total Category Formula
Use this solution when each row should be categorized based on the cumulative total from A1 down to the current row.
Fix Nested IF and AND Functions in WPS Spreadsheets
WPS Spreadsheets supports the same IF, AND, and SUM functions as Microsoft Excel, so you can correct the nested formula in the same workbook without switching tools.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx file, and click the worksheet that contains the formula.
- 2. Select the formula cell: Click the cell where the corrected result should appear, such as B6 for a fixed total or B1 for a running total.
- 3. Enter the corrected formula: Type =IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<350),"A",IF(SUM(A1:A6)<700,"A","B")) and press Enter.
- 4. Apply the running total version if needed: For row-by-row evaluation, enter the expanding SUM($A$1:A1) version in B1 and drag the fill handle down to B6.
- 5. Save the corrected workbook: Press Ctrl+S to save the file and confirm that the total 446 returns the expected 350-to-700 range result.
Frequently Asked Questions
Why does my nested IF and AND formula return the wrong category for a total of 446?
Check the comparison order and boundary operators. In WPS Spreadsheets, use =IF(AND(SUM(A1:A6)>0,SUM(A1:A6)<350),"A",IF(SUM(A1:A6)<700,"A","B")) so 446 is caught by the second IF condition.
How do I make the formula use a running total instead of the whole range?
Use an expanding SUM range such as SUM($A$1:A1) in B1 and fill it down to B6. Each row then evaluates the cumulative total from A1 to the current row.
How do I include the 350 and 700 boundaries in the formula?
Change the comparison operators to handle inclusive limits. If 700 should be included, use SUM(A1:A6)<=700 or SUM($A$1:A1)<=700 in the relevant condition.
Can I use these formulas in WPS Spreadsheets if the file was created in Microsoft Excel?
Yes. WPS Spreadsheets is compatible with .xlsx files and supports IF, AND, and SUM, so you can edit the formulas without converting the workbook.
Why does my nested IF stop after the first condition?
Nested IF functions are evaluated from left to right. Place more specific range conditions before broader conditions, or use AND to combine multiple tests so WPS Spreadsheets continues to the next IF when the first test is false.
