How to Create a VBA Macro to Sum Data Using Two Criteria in Excel
Question details
The user needs a VBA macro to sum values across separate worksheets based on two specific criteria (Project and Product) using dynamic row and column ranges.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating totals across dynamic ranges where data positions change, requiring lookup by row and column headers.
- Observed behavior
- A need to locate intersecting cells for matching Project and Product headers, sum their values, and output the result to a Summary worksheet.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in your spreadsheet application.
Use a VBA Macro with Range.Find and Intersect
Write a VBA macro that loops through the Summary sheet, finds matching criteria on the data sheet, and sums the intersected values.
This method involves looping through your target Project and Product lists on a Summary sheet. By leveraging the Range.Find method, the macro locates the specific row and column on your Quantity sheet. The Intersect function is then used to pinpoint the exact cell where these two headers meet.
For datasets where criteria appear multiple times, you may need a custom FindAll helper function to return a collection of all matching cells.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor, then click Insert > Module to create a new script area.
Type 'Option Explicit' at the very top of your module to force variable declaration, which helps prevent bugs caused by spelling mistakes in variable names.
Write a Sub routine that sets references to your 'Summary' and 'Quantity' worksheets. Use a For loop to iterate through each row containing your target Project and Product criteria on the Summary sheet.
Inside the loop, use the Range.Find method on the Quantity sheet to search for the matching Project (to identify the row) and Product (to identify the column).
Use the Intersect(RowRange, ColumnRange) function to isolate the target cell. Add the cell's value to your running total, and once all matches are found, write the final sum back to the corresponding row in the Summary sheet.
Use Power Query as a No-Code Alternative
If maintaining a complex VBA macro is difficult, use Power Query to unpivot your data and sum values based on multiple criteria without writing code.
Handle Dynamic Ranges Easily in WPS Spreadsheet
WPS Spreadsheet provides robust support for complex data analysis. You can use advanced array formulas, PivotTables, or developer tools to sum data with multiple criteria effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm file containing the data.
- 2. Use SUMIFS for tabular data: If you prefer not to use VBA, go to the 'Formulas' tab and utilize the SUMIFS function to calculate totals with multiple criteria across standard ranges.
- 3. Create a PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'. Drag your Project and Product fields into the rows and columns to automatically group and sum your data without any code.
Frequently Asked Questions
Why use Intersect in a VBA macro for summing data?
The Intersect method allows you to easily find the exact cell where a specific row (e.g., Project) and column (e.g., Product) cross, extracting its value without requiring complex row and column index math.
What does 'Option Explicit' do in VBA?
Using 'Option Explicit' at the top of your VBA module forces you to declare all variables before using them. This helps prevent spelling mistakes and logical errors in your code, making debugging significantly easier.
Can I sum data with two criteria without using VBA?
Yes. If your data is in a standard tabular format, you can use the SUMIFS function. For data structured in a matrix with dynamic ranges, you can utilize Power Query or PivotTables to group and sum the data without writing any code.
