Function Problems

How to Use SUMIF to Return Matching Account Totals in Excel

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to pull and sum account totals from a source worksheet into a destination worksheet by matching specific account numbers.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a budget workbook where financial data needs to be aggregated across different worksheets based on matching account numbers.
Observed behavior: The user is seeking the correct formula structure to automatically find account numbers in a source sheet and return their corresponding summed totals to the current worksheet.

Before you start

Ensure that both your source and destination worksheets are located in the same workbook, and verify that the account numbers in both sheets share the exact same formatting (either both as text or both as numbers).

Solution 1Recommended

Apply the SUMIF Function across Worksheets

Use Excel's SUMIF function to reference data on another sheet, match your specified criteria, and calculate the total sum for each account.

The SUMIF function is specifically designed to sum values in a range that meet a single criterion. When dealing with budget workbooks, it allows you to cross-reference account numbers between sheets seamlessly without manual calculations.

Select the destination cell

Click on the cell in your destination worksheet (e.g., your summary sheet) where you want the total for a specific account to be displayed.

Enter the SUMIF formula

Type the formula =SUMIF(Source!A:A, A2, Source!B:B). In this structure, 'Source!A:A' references the column with account numbers in your source sheet, 'A2' is the cell containing the account number you are matching on your current sheet, and 'Source!B:B' references the column containing the totals.

Apply to other accounts

Press Enter to calculate the total for that specific account. You can then click and drag the fill handle (the small square at the bottom-right of the cell) down to apply this formula to the remaining account numbers in your summary sheet.

Formula Syntax Reminder: Make sure to replace 'Source' with the actual name of your source worksheet. If your worksheet name contains spaces (e.g., Source Data), you must enclose it in single quotation marks within the formula, like this: =SUMIF('Source Data'!A:A, A2, 'Source Data'!B:B).

Efficient Spreadsheet Data Management

Use WPS Spreadsheet to Aggregate Your Financial Data

WPS Spreadsheet fully supports the SUMIF function and all essential data analysis tools, allowing you to seamlessly manage budget workbooks, calculate totals across sheets, and process complex financial data for free.

1. Open your workbook: Launch WPS Spreadsheet and open your budget file containing both the source data and the summary worksheets.
2. Insert the function: Navigate to the destination cell, click on the 'Formulas' tab, and select 'Insert Function' to search for SUMIF, or simply type =SUMIF( directly into the cell.
3. Define your parameters: Highlight your criteria range from the source sheet, click your criteria cell on the current sheet, and then highlight the sum range on the source sheet.
4. Calculate and drag: Press Enter to execute the formula, then click and drag the bottom-right corner of the cell to autofill the totals for the rest of your accounts.

100% compatible with Microsoft Excel formulas, functions, and XLSX formats.Free, lightweight, and fast to load even with large datasets and multiple sheets.Clean, familiar user interface that requires no learning curve to use complex formulas.Built-in financial templates to help streamline your budget and account management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIF formula returning zero even though the account numbers match?

This usually happens if the account numbers in the source and destination sheets have different formats (for example, one is formatted as text and the other as a number), or if there are hidden trailing spaces. You can use the TRIM function to remove spaces or ensure both columns are formatted uniformly.

Can I use SUMIFS instead of SUMIF for this task?

Yes. If you need to match multiple criteria (such as matching both an account number and a specific month), you should use the SUMIFS function instead. Keep in mind that the syntax for SUMIFS is different, as the sum_range is the first argument rather than the last.

How do I reference a worksheet name with a space in my SUMIF formula?

If your worksheet name contains spaces, you must wrap the sheet name in single quotation marks within the formula. For example, if your sheet is named Budget 2023, your formula should look like this: =SUMIF('Budget 2023'!A:A, A2, 'Budget 2023'!B:B).

Excel & Data Problems