Function Problems

How to Sum the Last 12 Nonblank Cells in an Excel Row

Adam Davis Oct 15, 2026 870 views

Question details

The user needs to calculate the sum of the most recent 12 numeric values in a specific row while ignoring any empty cells.

How to Sum the Last 12 Nonblank Cells in an Excel Row

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking rolling totals, such as the last 12 months of sales or expenses, in a dataset where some intervening months might have missing or blank data.
Observed behavior: Calculating a dynamic sum of exactly 12 non-empty numeric cells requires specific formula structures depending on the software version, as newer dynamic array functions are not universally available.

Before you start

Verify which version of Excel you are using. Modern array functions like FILTER are available in Microsoft 365 and Excel 2021, while Office 2019 and older require legacy formulas.

Solution 1Recommended

Use Dynamic Array Formulas (Microsoft 365 & Excel 2021)

Utilize the modern FILTER and TAKE functions to easily extract and sum the last 12 non-empty cells in a row.

This is the most efficient and readable method. It automatically filters out empty cells and slices the remaining array to get the final 12 values.

Select the target cell

Click on the cell where you want the rolling 12-period total sum to appear.

Enter the formula

Type the formula =SUM(TAKE(FILTER(A2:Z2,A2:Z2<>""),,-12)) into the formula bar. Adjust the range A2:Z2 to match the actual row containing your data.

Execute the calculation

Press the Enter key. The formula will automatically extract the values that are not blank, grab the last 12 from the list, and sum them together.

Use Dynamic Array Formulas (Microsoft 365 & Excel 2021)

Function Availability: If you receive a #NAME? error, your software version does not support these modern functions. Please proceed to the legacy formula solution below.

Solution 2

Use Legacy Functions (Office 2019 and Older)

Use a combination of older functions like LARGE, COLUMN, and INDEX to calculate the sum when FILTER is not supported.

Efficient Data Calculation with WPS

Use WPS Spreadsheet for Advanced Array Formulas

WPS Office fully supports modern array functions, allowing you to easily calculate dynamic sums like the last 12 non-blank cells without complex legacy workarounds.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the row data.
2. Select the destination cell: Click the cell where you want to display the rolling 12-month sum.
3. Input the dynamic formula: Enter =SUM(TAKE(FILTER(A2:Z2,A2:Z2<>""),,-12)) and press Enter. WPS Spreadsheet will instantly calculate the correct total.

Full compatibility with Microsoft Excel formulas and .xlsx files.Built-in support for advanced dynamic array functions like FILTER.Free and lightweight office suite for daily data processing.Familiar tabbed interface makes formula writing simple and intuitive.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error when using the FILTER function?

The #NAME? error occurs if your spreadsheet software version does not support the FILTER function. This function was introduced in Microsoft 365 and Excel 2021. For older versions like Office 2019, you must use legacy array functions or upgrade your software.

How can I sum the last 12 values in a column instead of a row?

You can adapt the formula by changing the range and the TAKE parameter. For a column range like A2:A100, use =SUM(TAKE(FILTER(A2:A100,A2:A100<>""),-12)). Notice the omission of the second comma before -12, which targets rows instead of columns.

Does this formula ignore cells with text or zero values?

The formula =SUM(TAKE(FILTER(A2:Z2,A2:Z2<>""),,-12)) only ignores completely blank cells. If a cell contains a zero or text, it will be included in the last 12 count. If you want to strictly evaluate numbers greater than zero, update the FILTER condition to A2:Z2>0.

Excel & Data Problems