Function Problems

How to Use Excel SUMIFS Formula to Total Amounts by Account Number

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs to calculate the total transaction amounts associated with specific account numbers when the account numbers and transaction amounts are stored in separate columns.

How to Use the Excel SUMIFS Formula to Total Amounts by Account Number

Product: Excel
Device & OS: not provided
Scenario: Summarizing financial or transaction data by specific account numbers for reporting or analysis purposes.
Observed behavior: The user requires a formula or method to conditionally sum values from an amount column based on matching criteria in an account number column.

Before you start

Ensure that your account numbers and transaction amounts are organized in separate columns, and verify that there are no merged cells within your data ranges to ensure accurate formula calculations.

Solution 1Recommended

Calculate Totals Using the SUMIFS Function

The standard and most efficient method for summing values conditionally based on specific account numbers.

The SUMIFS function adds all arguments that meet multiple criteria. Even with a single criterion like an account number, SUMIFS is highly recommended for its flexibility.

Identify data columns

Locate the column containing your account numbers (e.g., Column H) and the column containing your transaction amounts (e.g., Column I).

Select a target cell

Click on an empty cell where you want the total to appear (e.g., C2), ideally next to the specific account number you want to summarize (e.g., in B2).

Enter the SUMIFS formula

Type the formula =SUMIFS(I:I,H:H,B2) and press Enter. This tells Excel to sum values in column I where column H matches the value in cell B2.

Fill the formula down

Click the small square at the bottom-right corner of the cell containing your formula and drag it down to calculate totals for other account numbers in your list.

Calculate Totals Using the SUMIFS Function

Microsoft 365 Dynamic Arrays: If you are using Microsoft 365, you can use a spilled array formula like =SUMIFS(I:I,H:H,B2:B43). This single formula will automatically populate totals for the entire range of account numbers without needing to be dragged down.

Solution 2

Summarize Amounts Using a PivotTable

An excellent alternative when you need a quick overview of all account totals without writing any formulas.

Effortlessly Summarize Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, fully supporting standard Excel functions like SUMIFS and dynamic arrays to help you summarize account data quickly and accurately.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your transaction data.
2. Select the result cell: Click the cell where you wish to calculate the total for a specific account.
3. Input the formula: Type =SUMIFS( and select your sum range (amounts), criteria range (account numbers), and your specific account number cell.
4. Calculate: Press Enter to instantly view your calculated total, and drag the fill handle to apply it to other accounts.

Fully compatible with Microsoft Excel formulas and formats (.xlsx).Includes intuitive data visualization and advanced PivotTable features.Free, lightweight, and fast alternative for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use SUMIF instead of SUMIFS for a single account number condition?

Yes, if you only have one condition, both SUMIF and SUMIFS will work. However, SUMIFS is generally recommended as best practice because the syntax order allows you to easily add more conditions later without rewriting the entire formula.

Why is my SUMIFS formula returning 0 instead of the actual total?

This typically happens if the data types don't match. For example, your account numbers might be stored as text in the data column but as numbers in your criteria cell. Check for leading or trailing spaces, and ensure both ranges share the same cell formatting.

How do I use SUMIFS with partial account numbers?

You can use wildcard characters in your criteria. For example, if you want to sum amounts for all accounts starting with '100', you can use the formula =SUMIFS(I:I,H:H,"100*"). The asterisk (*) represents any sequence of characters.

Excel & Data Problems