Calculation Issues

How to Sum Monthly Sales by Product with Date Criteria in Excel

Natalie Taylor Sep 27, 2026 868 views

Question details

The user needs to calculate monthly sales figures for specific products based on complex date criteria, including conditional start dates, end dates, and partial-month periods, while keeping the worksheet scalable for future months.

Sum Monthly Sales by Product with Date Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Building a dynamic and scalable monthly sales report that aggregates revenue by product across specific conditional date ranges.
Observed behavior: The goal state requires dynamically summing sales using the later of two possible start dates, capping at an end date, and accounting for partial months, all while allowing easy addition of future columns.

Before you start

Ensure your raw sales data contains properly formatted dates rather than text, and that your summary table uses actual date values for its month headers.

Solution 1Recommended

Calculate Monthly Sales Using SUMPRODUCT and Custom Date Headers

Use a combination of real date headers formatted as text and the SUMPRODUCT function to dynamically evaluate multiple date conditions and sum sales by product.

This approach utilizes Excel's SUMPRODUCT function, which is highly effective for evaluating multiple arrays and criteria simultaneously. By converting your month headers into real dates, the formula can easily compare transaction dates against the reporting periods.

Set Up Real Date Month Headers

In your summary table's header row, enter the first day of each month (e.g., 1/1/2024, 2/1/2024). Right-click the cells, select 'Format Cells', navigate to the 'Custom' category, and type 'MMM' to display only the abbreviated month name while retaining the underlying date value.

Construct the SUMPRODUCT Formula

Select the first calculation cell for your product. Enter a formula similar to =SUMPRODUCT((ProductRange=$A2)*(DateRange>=MAX($B2,$C2))*(DateRange<=$D2)*(DateRange>=E$1)*(DateRange<=EOMONTH(E$1,0)), SalesRange). This evaluates the product match, the later of the two start dates, the end date limit, and the specific month timeframe.

Account for Partial Months

If you need to prorate sales for partial months, modify your SalesRange calculation within the formula to divide the total monthly value by the number of days in the month, then multiply by the active days identified by your MIN/MAX date limits.

Copy Down and Across

Ensure your data ranges are locked with absolute references (e.g., $A$2:$A$100). Click and drag the fill handle at the bottom-right corner of the cell to copy the formula down for all products and across for all future month columns.

Calculate Monthly Sales Using SUMPRODUCT and Custom Date Headers

Scalability: Because the formula references the date headers dynamically, you can easily add new columns for future months by dragging the month headers to the right and copying the formula over.

Efficient Data Calculation in WPS Spreadsheet

Easily Sum Monthly Sales with Complex Criteria in WPS Office

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, MAX, MIN, and EOMONTH, allowing you to seamlessly manage complex sales reports with dynamic date criteria.

1. Open Your Sales Data: Launch WPS Spreadsheet and open your .xlsx sales data workbook.
2. Input the Formula: Select the target cell in your summary table and type your SUMPRODUCT formula referencing the product names and date ranges.
3. Apply Array Formatting: Press Enter to calculate the result. WPS Spreadsheet processes arrays natively, instantly returning your complex calculation.
4. Fill the Table: Drag the fill handle across your month headers and down your product list to complete your dynamic sales report.

Seamlessly processes complex SUMPRODUCT formulas for multi-criteria sales calculations.100% compatible with Microsoft Excel file formats (.xlsx), ensuring accurate data rendering.Free to use with a lightweight installation, intuitive interface, and fast performance.

Frequently Asked Questions

Why does my SUMPRODUCT formula return a #VALUE! error?

This typically occurs if the ranges in your SUMPRODUCT formula are not the exact same size (e.g., comparing rows 2:100 with rows 2:90), or if there is text in a range where a numeric calculation is expected.

How do I find the later of two start dates in Excel formulas?

You can use the MAX function to determine the most recent date. For example, placing MAX($B2, $C2) inside your formula will automatically return the later of the two dates located in B2 and C2.

Can I use SUMIFS instead of SUMPRODUCT for this calculation?

Yes, SUMIFS can handle multiple criteria including date ranges (using ">=" and "<="). However, SUMPRODUCT is often preferred when you need to perform inline array calculations, such as evaluating the MAX of two columns directly within the condition logic.

How do I easily add future months to my report?

If you used real date values in your header row and locked your data ranges with absolute references (like $A$2:$A$100), you can simply drag the last month column header to the right to generate the new month, then copy the formula into the new column.

Excel & Data Problems