Function Problems

How to Sum Product Sales Across Multiple Years in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate and consolidate sales totals for multiple products in specific months from a dataset spanning multiple years.

Product: Excel
Device & OS: not provided
Scenario: Products are listed in rows, and months are arranged across various columns. The user wants to aggregate cross-year monthly data into a single summary table.
Observed behavior: The user needs to look up and sum results for multiple products for every month of the year, extracting totals from a wide data table into an organized monthly report.

Before you start

Ensure your dataset has consistent formatting, with product names correctly spelled in a single column and month headers uniformly labeled across the top row.

Solution 1Recommended

Use an Array Formula to Sum Sales by Product and Month

Use a combination of the UNIQUE function to set up your summary headers, and a multi-condition SUM array formula to aggregate sales from a 2D range.

Traditional functions like SUMIFS only work on single-column or single-row sum ranges. When your data spans multiple columns and rows, you must use an array formula to multiply the row and column criteria against the data matrix.

Extract unique products and months

In a new area of your worksheet, use the formula `=UNIQUE(A2:A8)` to create a vertical list of unique products. Then, use `=UNIQUE(B1:Y1, TRUE)` in an adjacent row to generate a horizontal list of unique months.

Enter the conditional SUM formula

Select the first empty cell in your new results table (e.g., intersecting the first unique product and month). Enter the formula: `=SUM(($A$2:$A$8=$A11)*($B$1:$Y$1=B$10)*($B$2:$Y$8))`. Note: In older Excel versions, press Ctrl+Shift+Enter to apply this as an array formula.

Fill the formula across the table

Click the small square at the bottom-right corner of the cell containing the formula and drag it across all columns and down all rows to populate the entire summary table.

Calculate specific totals

To calculate the total for all products in a specific month, use `=SUM(B12:B18)` at the bottom of the column. To find the total for a single product across all months, use `=SUM(B12:M12)` at the end of the row and drag down.

Absolute References are Crucial: Pay close attention to the dollar signs ($) in the formula. Ranges like $A$2:$A$8 must be locked (absolute) so they do not shift when you drag the formula. Criteria like $A11 (column locked) and B$10 (row locked) use mixed references to adjust correctly.

Analyze Data Effectively

Easily Sum Complex Data Across Multiple Years with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions and modern dynamic formulas like UNIQUE, making it easy to calculate cross-year totals. It provides a familiar interface and operates seamlessly with complex Excel data.

1. Open your dataset: Launch WPS Office and open your spreadsheet containing the multi-year sales data.
2. Set up your summary table: Use the UNIQUE function to automatically list your individual products and months in a new summary area.
3. Apply the array formula: Type your multi-condition SUM formula into the first cell of your summary table to calculate the cross-referenced data.
4. Drag to complete: Use the fill handle to drag the formula across your rows and columns to instantly compute the rest of the table.

Fully compatible with Microsoft Office Excel (.xlsx) formats and formulas.Supports advanced array formulas and multi-criteria SUM calculations.Lightweight, fast, and completely free for daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUM formula returning an error or zero?

This typically happens if the formula isn't evaluated as an array formula. In older spreadsheet versions, you must press Ctrl+Shift+Enter after typing the formula instead of just Enter. Additionally, double-check that your reference ranges are identical in size.

Can I use SUMIFS instead of this array formula?

SUMIFS is designed for calculating sums where the sum range is a single column or row. Because your data is spread across a 2D matrix (multiple columns representing different years/months), the standard SUMIFS function will not work. You must use a SUM array formula or SUMPRODUCT.

How do I calculate the grand total for all products across all years?

You can calculate a grand total by using a basic SUM formula that encompasses your entire raw data range, such as `=SUM(B2:Y8)`, or by summing the final totals row/column from your newly created summary table.

Excel & Data Problems