Function Problems

How to Use Excel COUNTIFS for a Rolling Six-Month Report

Maira Mehtab Oct 23, 2026 868 views

Question details

The user needs to calculate monthly referrals for a rolling six-month period without overwriting historical counts as the reporting period moves forward.

How to Use Excel COUNTIFS for a Rolling Six-Month Report

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic statistics sheet where historical data (e.g., February 2022) is calculated based on specific year/month inputs and categorized criteria (e.g., 'Cyber').
Observed behavior: The user is struggling to write a formula that correctly counts occurrences in a tracking sheet during a specific month to maintain fixed historical records while the six-month reporting window updates.

Before you start

Ensure that your source tracking data contains properly formatted date columns and that your target year and month variables are placed in designated cells (e.g., Year in A23, Month in A25).

Solution 1Recommended

Combine COUNTIFS with DATEVALUE and EOMONTH

Use a multi-criteria COUNTIFS formula that calculates the exact first and last day of the target month to filter historical records dynamically.

By dynamically calculating the first day of the target month and determining its last day using EOMONTH, you can define strict date boundaries. This guarantees that historical statistics remain static for past months, even as your rolling report moves forward in time.

Define the target start date

Use the DATEVALUE function to combine your target month and year into a valid date serial number. For example, if A25 holds the month name and A23 holds the year, the formula for the first day is DATEVALUE("1-"&A25&"-"&A23).

Calculate the end date of the month

Wrap the DATEVALUE formula inside the EOMONTH function to find the final day of that specific month. The syntax will be EOMONTH(DATEVALUE("1-"&A25&"-"&A23), 0), where 0 indicates the current month.

Construct the COUNTIFS formula

In your statistics sheet, enter =COUNTIFS(Tracking!DateRange, ">=" & StartDate, Tracking!DateRange, "<=" & EndDate, Tracking!CategoryRange, "Cyber") substituting the date formulas from the previous steps for StartDate and EndDate.

Apply and drag the formula

Press Enter to execute the calculation. Because this formula targets a specific month and year via cell references rather than a volatile function like TODAY(), the past historical counts will remain accurate as you extend the report to future months.

Combine COUNTIFS with DATEVALUE and EOMONTH

Static Historical Data Preserved: This formula prevents your historical six-month data from changing dynamically because it anchors the count to the explicit year and month specified in your statistics reference cells.

Solution 2

Use PivotTables and a Timeline

Utilize Excel's built-in PivotTable features alongside a Timeline slicer to filter rolling six-month periods visually without complex formulas.

Generate Dynamic Reports with WPS Office

Create Advanced Date Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports complex date formulas like COUNTIFS, EOMONTH, and DATEVALUE, allowing you to quickly build dynamic rolling reports and analyze historical data with precision.

1. Open your data file: Launch WPS Spreadsheet and open the .xlsx file containing your tracking and statistics sheets.
2. Select the target cell: Click on the specific cell in your statistics report where you want the historical monthly count to appear.
3. Enter the formula: Type the standard =COUNTIFS() formula combining the EOMONTH and DATEVALUE functions to define your exact monthly boundaries.
4. Calculate and verify: Press Enter to calculate the dynamic rolling data immediately without macro errors.

100% compatible with Microsoft Excel formulas and date functionsFree and lightweight alternative for powerful data analysisIntuitive interface for building dynamic PivotTables and chartsSeamlessly opens, edits, and saves .xlsx report files

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTIFS formula return 0 when filtering by date?

This usually happens if the dates in your source criteria range are formatted as text rather than valid serial date numbers. Highlight your date column, right-click, select 'Format Cells', and ensure it is formatted as a 'Date'.

How does the EOMONTH function work in date criteria?

The EOMONTH function calculates the last calendar day of a month for a specified date. By using EOMONTH(start_date, 0), it returns the exact end date of the target month, which is essential for ensuring your COUNTIFS logic captures every day of that month.

Can I reference a different worksheet inside the COUNTIFS formula?

Yes. You can reference data across different sheets by adding the sheet name followed by an exclamation mark before the cell range, such as Tracking!A:A. When building the formula, clicking directly on the range in the other sheet will populate this syntax automatically.

Excel & Data Problems