Calculate Quarterly Cumulative Totals Based on a Selected Month in Excel
Question details
Create a formula to calculate cumulative quarterly totals that update dynamically based on a user-selected month.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a dynamic data report where selecting a month (e.g., February) aggregates data for the quarter up to that month (e.g., January plus February).
- Observed behavior
- The goal is to return the sum of values from the beginning of the quarter through the end of the chosen month, referencing data from another worksheet.
Ensure your source data contains actual date values rather than just text labels for months, as Excel's date functions require valid serial numbers to properly calculate the end of a month.
Use SUMIFS and EOMONTH for Dynamic Quarterly Totals
This approach uses built-in date functions to define a dynamic date range, aggregating records from the start of the quarter to the end of your selected month.
By formatting actual dates to display only the month name, you can create a user-friendly dropdown while keeping the underlying date logic intact. The SUMIFS function will then evaluate multiple criteria across different worksheets.
Select a cell (e.g., A1). Go to the Data tab, click Data Validation, and choose List. Reference a range of actual dates (e.g., 1/1/2023, 2/1/2023) and apply a custom number format 'mmmm' so they display as January, February, etc.
In your result cell, type `=SUMIFS(`. First, select the range containing the values you want to sum on your data worksheet.
Select the date column in your data worksheet as the criteria range. For the criteria, enter `">="&DATE(2023,1,1)` (assuming the first quarter starts on January 1st) to ensure only dates from the beginning of the quarter are included.
Select the date column again for the second criteria range. For the criteria, use the EOMONTH function to capture the last day of the selected month by entering `"<="&EOMONTH(A1,0)`.
Use SUM and OFFSET for Data Arranged in Sequential Columns
If your worksheet organizes data horizontally with each month in its own column rather than as individual date records, you can use OFFSET to dynamically adjust the width of the range to sum.
Easily Calculate Dynamic Quarterly Reports in WPS Spreadsheet
WPS Spreadsheet provides full support for advanced date and math functions like SUMIFS, EOMONTH, and OFFSET, making it easy to build dynamic quarterly reports with data spanning multiple worksheets.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cross-sheet data.
- 2. Set up Data Validation: Select the cell for your dropdown, go to the Data tab, select Data Validation, and link it to your list of date values.
- 3. Apply Custom Formatting: Right-click the dropdown cell, select Format Cells, and apply the Custom format 'mmmm' to display month names.
- 4. Enter the SUMIFS formula: Type your SUMIFS formula utilizing the EOMONTH function to instantly calculate the cumulative totals.
Frequently Asked Questions
Why is my EOMONTH formula returning a 5-digit number instead of a date?
Excel and WPS Spreadsheet store dates as serial numbers (e.g., 44927). If your cell displays this raw number, simply select the cell, go to the Home tab, and change the Number Format to Short Date.
Can I base the calculation on plain text month names instead of actual dates?
Yes, but you will need to convert the text to a date value first. You can use the DATEVALUE function (e.g., `=DATEVALUE(A1&" 1, 2023")`) to convert a text string like 'February' into a valid date before nesting it inside your EOMONTH function.
How do I dynamically calculate the start date of the quarter based on the selected month?
If you don't want to hardcode the quarter's start date, you can dynamically find the first day of the current quarter using this formula: `=DATE(YEAR(A1), FLOOR(MONTH(A1)-1, 3)+1, 1)`. This will automatically adjust depending on the month selected in cell A1.
