Function Problems

Excel Formula to Sum Values After the Last Non-7 Entry

Phi Hung Vo Sep 27, 2026 869 views

Question details

The user needs an Excel formula to calculate the sum of a range starting from the last occurrence of a cell that does not equal a specific value (e.g., 7) through the end of that range.

How to Sum Values After the Last Non-7 Entry in Excel

Product: Spreadsheets
Device & OS: not provided
Scenario: Calculating a conditional sum based on the position of the last specific non-matching value in a horizontal dataset.
Observed behavior: The user wants to dynamically identify the last cell that is not equal to 7 in a range (like C4:J4) and sum all values from that point to the end of the range.

Before you start

Ensure your spreadsheet software supports dynamic array functions like FILTER, XLOOKUP, and SEQUENCE, as these are required for this advanced calculation.

Solution 1Recommended

Use SUM, FILTER, and XLOOKUP Functions

This method dynamically finds the position of the last non-7 entry and filters the array to sum only the values from that point forward.

This formula combines several modern spreadsheet functions. XLOOKUP is used to search in reverse (from right to left) to find the position of the last value that isn't 7. FILTER then isolates the range starting from that column, and SUM calculates the total of those isolated values.

Select the target cell

Click on the cell where you want the final sum result to appear.

Input the formula

Type the following formula into the formula bar: =SUM(FILTER(C4:J4,COLUMN(C4:J4)>=XLOOKUP(TRUE,C4:J4<>7,SEQUENCE(,COLUMNS(C4:J4)),1,-1)))

Adjust the cell references

Change instances of 'C4:J4' in the formula to match the actual row and column range of your specific dataset.

Calculate the result

Press the Enter key to execute the formula and view the sum of the filtered range.

Formula Breakdown: The '-1' at the very end of the XLOOKUP function is a crucial parameter, as it instructs the function to search the array in reverse order (from the last entry to the first).

Advanced Formula Support

Calculate Complex Formulas for Free with WPS Spreadsheet

WPS Office provides robust support for advanced dynamic array functions like XLOOKUP, FILTER, and SEQUENCE, allowing you to solve complex data analysis problems quickly and easily.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
2. Select the destination cell: Click on the cell where you want the conditional sum to be calculated.
3. Enter the dynamic formula: Type your combined SUM and XLOOKUP formula and press Enter to instantly get the results.
4. Save with seamless compatibility: Save your work in the standard .xlsx format, ensuring perfect compatibility with Microsoft Office users.

Fully compatible with Microsoft Excel formulas and functions, including dynamic arrays.Execute complex combinations of XLOOKUP, FILTER, and SUM without lag.Free, lightweight, and features a familiar user interface for a seamless transition.

microsoft office alternative - wps office

Frequently Asked Questions

What if my data is in a column instead of a row?

If your data is arranged vertically (for example, C4:C20), you need to change the COLUMN function to ROW, change COLUMNS to ROWS, and adjust the range references accordingly in the formula.

Why does the formula return a #NAME? error?

Functions like FILTER, XLOOKUP, and SEQUENCE are modern dynamic array functions. If you are using an older version of spreadsheet software that does not support them, the system will not recognize the function names and will return a #NAME? error. Upgrading to the latest WPS Office resolves this.

Can I change the specific value from a number to text?

Yes, you can modify the condition. Simply replace the '7' in the 'C4:J4<>7' portion of the formula with your desired text enclosed in quotation marks, such as 'C4:J4<>"Completed"'.

Excel & Data Problems