How to Create Subtotals from Multiple Columns in Excel
Question details
The user needs to consolidate and calculate subtotals for accounts that appear across multiple different columns.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing financial or accounting data where the same account entries and their corresponding amounts are distributed across two separate column pairs (e.g., Account A/Amount A and Account B/Amount B).
- Observed behavior
- The goal is to generate a consolidated account list and a unified total amount without needing to manually stack or merge the original columns into one tall list.
Ensure your account columns have exact matching names without extra trailing spaces, as the SUMIF function requires exact text matches to calculate correctly.
Use SUMIF Function to Add Multiple Columns
Combine multiple SUMIF functions to calculate totals from separate column ranges based on a consolidated account list.
The SUMIF function allows you to add values based on a specific criterion. By adding two SUMIF formulas together, you can evaluate two separate account columns and sum their respective amounts seamlessly.
Extract all unique account names from both 'Account A' and 'Account B' columns and paste them into a new column (e.g., Column E).
In the cell next to your first consolidated account (e.g., F2), type =SUMIF($A$2:$A$5, E2, $B$2:$B$5). This totals the amounts from the first account column.
Append the second range evaluation to the existing formula by adding a plus sign: +SUMIF($C$2:$C$5, E2, $D$2:$D$5).
Press Enter to calculate the total for the first account, then drag the fill handle down to apply this combined formula to the rest of your consolidated account list.
Use Power Query to Unpivot and Summarize
Use Excel's Power Query feature to transform and stack the columns dynamically for easier pivot table creation.
Consolidate Data Easily with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced formulas like SUMIF, allowing you to quickly consolidate multi-column data. It is a powerful, free alternative to Excel with a familiar interface.
- 1. Open your data file: Launch WPS Spreadsheet and open your financial or accounting document.
- 2. Create a unique account list: Copy all account names from your multiple columns, paste them in a new column, and use the 'Remove Duplicates' feature under the Data tab.
- 3. Input the combined formula: Use the combined =SUMIF()+SUMIF() formula next to your unique list to calculate the subtotals.
- 4. Fill the column: Drag the fill handle downward to apply the subtotals across all accounts instantly.
Frequently Asked Questions
Why is my SUMIF formula returning zero even though the account exists?
This usually happens due to mismatched text, such as extra trailing spaces or slight spelling differences between the columns. Use the TRIM function on your account columns to remove invisible spaces, or ensure the text matches exactly.
Can I use SUMIFS instead of SUMIF for multiple columns?
The SUMIFS function is designed to evaluate multiple conditions within the same range, not across different column sets. For distinct column pairs (Account A/Amount A and Account B/Amount B), adding separate SUMIF functions together is the correct approach.
How do I easily get a list of unique accounts from two different columns?
You can copy the data from both account columns, paste them one under the other in a new temporary column, and then use the 'Remove Duplicates' feature found in the Data tab to get a clean, consolidated list.
