Function Problems

How to Sum or Average the Last Two Nonblank Values in Excel

John Wilson Oct 10, 2026 869 views

Question details

Calculate the sum or average of the last two numeric entries in a column that contains blank cells.

How to Sum or Average the Last Two Nonblank Values in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating totals or averages for the most recent data entries in a dynamically updated column with gaps.
Observed behavior: The goal is to dynamically extract the final two non-empty numerical values from a range and compute their sum or average.

Before you start

Ensure you are using a recent version of Microsoft 365, Excel Online, or a compatible spreadsheet software like WPS Office that supports modern dynamic array functions such as TOCOL and TAKE.

Solution 1Recommended

Use TOCOL and TAKE Functions (Microsoft 365)

The most efficient way to extract and calculate the last two nonblank values using modern dynamic array functions.

The TOCOL function is perfect for ignoring empty cells, while the TAKE function can effortlessly grab elements from the end of an array.

Select Output Cell

Click on the empty cell where you want your calculated result to appear.

Enter the SUM Formula

Type the formula =SUM(TAKE(TOCOL(A1:A17,1),-2)), replacing A1:A17 with your actual data range. The second argument '1' in TOCOL tells it to ignore blanks.

Calculate the Average Instead

If you need the average, type =AVERAGE(TAKE(TOCOL(A1:A17,1),-2)) in your desired output cell.

Confirm Formula

Press Enter to execute the function and display your result.

Dynamic Updates: As you add new numbers to the bottom of your column, this formula will automatically update to reflect the newest last two entries.

Solution 2

Use FILTER, INDEX, and COUNT Functions

A fallback method for calculating the sum if TOCOL and TAKE are not available in your spreadsheet version.

Advanced Formulas in WPS Spreadsheet

Calculate Dynamic Ranges Easily with WPS Office

WPS Spreadsheet offers powerful data analysis tools and robust support for advanced dynamic array functions. You can seamlessly calculate moving averages and dynamic sums without complex workarounds.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your data column.
2. Select a Blank Cell: Click on an empty cell where you want the final sum or average to be displayed.
3. Input the Dynamic Formula: Type =SUM(TAKE(TOCOL(A1:A20,1),-2)) to instantly target the last two non-empty cells.
4. View the Dynamic Result: Press Enter. The calculation will update dynamically whenever new data is appended to the list.

Fully compatible with Microsoft Excel formulas and .xlsx formatsSupports advanced dynamic array functions for complex data analysisLightweight application with a familiar, easy-to-navigate user interfaceFree to use with built-in templates and productivity tools

microsoft office alternative - wps office

Frequently Asked Questions

Can I adapt this formula to sum the last 5 values instead of 2?

Yes, simply change the -2 in the TAKE function to -5. For example, =SUM(TAKE(TOCOL(A1:A17,1),-5)) will extract and sum the last five nonblank values.

What happens if there is only one numeric value in the range?

If there is only one value available in the filtered array, the TAKE function will return just that single value without throwing an error, and the SUM or AVERAGE will be based solely on it.

How do I make this formula work for data in a row instead of a column?

The TOCOL function inherently converts a 2D array or a row into a single column. If your data is in a row, TOCOL will still stack it vertically, allowing the exact same TAKE function logic to work seamlessly.

Will this formula ignore cells that contain text strings?

The TOCOL function with the argument 1 only ignores blank cells. If your range contains text, you might get a #VALUE! error when attempting to calculate the sum. To ignore text, you must combine FILTER with the ISNUMBER function before applying TAKE.

Excel & Data Problems