How to Sum Forecast Values for Current and Next Month in Excel/WPS
Question details
The user needs to create a dynamic dashboard formula that sums forecast values specifically for the current and following month, while excluding certain task codes and unrelated options.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a financial or project dashboard that requires dynamic data aggregation based on rolling date boundaries.
- Observed behavior
- A reliable formula approach is needed to handle dynamic rolling months and apply multiple inclusion and exclusion criteria simultaneously.
Ensure that your month headers or reference cells are formatted as valid dates (e.g., the 1st of the month) rather than plain text, as date functions require real serial numbers to calculate boundaries correctly.
Use SUMIFS with EOMONTH for Dynamic Date Boundaries
The SUMIFS function combined with EOMONTH is the most efficient way to sum values within a specific date range while applying multiple inclusion and exclusion criteria.
By storing your months as actual dates, you can use the EOMONTH (End of Month) function to dynamically calculate the first and last day of any given month. This allows SUMIFS to capture exactly the data that falls within the current or next month.
Ensure you have a reference cell (e.g., A2) containing a valid date representing the selected month.
Start your formula with =SUMIFS( and select your forecast value column as the sum_range.
To sum the current month, add the start date criteria: Criteria_Range1, ">="&EOMONTH($A$2,-1)+1. Then add the end date criteria: Criteria_Range2, "<"&EOMONTH($A$2,1)+1.
To calculate the following month in a separate cell, adjust the offsets: ">="&EOMONTH($A$2,0)+1 for the start, and "<"&EOMONTH($A$2,2)+1 for the end.
Append exclusion criteria to the same SUMIFS formula using the not-equal operator. For example: Task_Code_Range, "<>TaskCode1", Task_Code_Range, "<>TaskCode2".

Use SUMPRODUCT for Complex Array Conditions
If your data layout prevents SUMIFS from working properly (e.g., working with 2D ranges or evaluating arrays without helper columns), SUMPRODUCT can evaluate arrays dynamically.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet offers powerful formula calculation capabilities, including advanced functions like SUMIFS, SUMPRODUCT, and EOMONTH. Its intuitive formula syntax highlights and error-checking tools make building complex dashboard calculations effortless.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your dashboard spreadsheet.
- 2. Select the Target Cell: Click on the cell where you want the forecast sum to appear.
- 3. Insert Function: Go to the Formulas tab, click 'Insert Function', and search for SUMIFS or SUMPRODUCT.
- 4. Input Arguments: Follow the on-screen prompts to input your sum range, date criteria using EOMONTH, and task code exclusions.

Frequently Asked Questions
Why is my SUMIFS formula returning zero for the date range?
This usually happens if your dates are stored as text instead of actual date values. Select your date cells, go to the Home tab, and format them as Short Date. Also, ensure your criteria strings are correctly concatenated with an ampersand, like ">="&EOMONTH(A2,-1)+1.
How do I exclude multiple task codes in SUMIFS?
You must repeat the criteria range and apply the <> (not equal to) operator for each task code individually. For example: Criteria_Range, "<>Code1", Criteria_Range, "<>Code2".
Can I dynamically reference the current system month without a helper cell?
Yes, you can replace the cell reference with the TODAY() function. For example, EOMONTH(TODAY(), -1) + 1 will always return the first day of the current system month, allowing your dashboard to update automatically.




