Function Problems

Excel Formula to Return the Four Largest Totals from Two Debit Columns

WPS Editor Sep 28, 2026 869 views

Question details

The user needs an Excel formula to combine debit amounts from two separate columns, sort the calculated totals in descending order, and extract the top four expense records along with their dates, names, and categories.

How to Extract the Four Largest Totals from Two Debit Columns in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Analyzing financial or expense data to dynamically identify the four highest total debits across multiple categories.
Observed behavior: The user requires a dynamic formula solution that calculates the sums row-by-row and correctly returns the top four corresponding rows, accurately handling potential ties.

Before you start

Ensure your source data is organized in a structured format without blank rows, and identify the exact column letters for your dates, names, categories, and the two debit columns.

Solution 1Recommended

Use TAKE, SORT, and HSTACK Functions

This is the most efficient dynamic array method to combine specific columns, sum the debits row-by-row, and extract the top four results instantly.

By nesting HSTACK, SORT, and TAKE, you can build a new virtual table in memory. HSTACK stitches your chosen columns together while simultaneously adding the two debit columns. SORT then orders this new table by the total column, and TAKE extracts the top N rows.

Select the destination cell

Click on the top-left cell where you want the extracted top four expenses to appear. Ensure there is enough empty space below and to the right for the results to spill over.

Enter the dynamic array formula

Input the formula: =TAKE(SORT(HSTACK(A2:B7,D2:D7+F2:F7,G2:G7),3,-1),4). In this example, A2:B7 contains Dates and Names, D2:D7 and F2:F7 are the two debit columns being added, and G2:G7 is the category.

Apply the formula

Press the Enter key. The formula will automatically spill the combined data, sorting the calculated totals descending and displaying only the first four rows.

Dynamic Updates: If any debit amount in your source data changes, this formula will automatically recalculate and update the top four list.

Solution 2

Handle Tied Totals Using FILTER and LARGE

Use this method if tied totals are causing the basic formula to exclude actual top-tier distinct values.

Advanced Spreadsheet Functions

Easily Calculate and Extract Top Totals in WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for modern array formulas like TAKE, SORT, and HSTACK, making complex data extraction tasks simple. You can seamlessly analyze your top debit totals with perfect compatibility.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your expense data.
2. Prepare the destination area: Select an empty cell where the top four results will be displayed, ensuring no data will be overwritten by the spilled array.
3. Input the array formula: Type the formula =TAKE(SORT(HSTACK(A2:B7,D2:D7+F2:F7,G2:G7),3,-1),4) into the formula bar, adjusting the cell references to match your specific layout.
4. Execute the calculation: Press Enter to instantly generate a sorted summary of your four highest expenses.

Full support for advanced dynamic array formulas like TAKE, FILTER, and HSTACKPerfect compatibility with Microsoft Excel .xlsx file formatsLightweight software that processes complex calculations quicklyFree to use with a familiar, easy-to-navigate interface

Frequently Asked Questions

Why does the formula return duplicates for tied values?

The TAKE function simply extracts the top N rows after sorting the array. If two or more rows have the exact same total, they will all be treated as individual rows. To extract unique top totals, you need to use the UNIQUE function or filter based on a distinct threshold.

What if my spreadsheet version doesn't support the TAKE or HSTACK functions?

If you are using an older spreadsheet version, you can achieve the same result by adding a helper column to your raw data. Create a new column that sums the two debit columns (=D2+F2), drag it down, then sort the entire table manually by this helper column and copy the top four rows.

How does the HSTACK function work in this formula?

HSTACK appends arrays horizontally. In this specific formula, it stitches together the original Date and Name columns with a dynamically calculated totals column (Debit 1 + Debit 2) and the Category column, creating a single unified table in memory.

Excel & Data Problems