How to count the number of occurrences per year/quarter/month/week in Excel?

When you need to quickly find out how many times a certain event occurred in a particular year or month, formulas offer a flexible and dynamic approach. By using built-in date functions together with SUMPRODUCT, you can directly calculate counts by year, month, or any combination thereof, making your summary accurate and automatically updated as source data changes. This approach works well in most routine analysis tasks for small to mid-sized datasets.

Select a blank cell where you want to display the count result, then enter the following formula:

=SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2))

After entering the formula, drag the cell's AutoFill handle downward to apply the formula to other rows as needed. As shown below:

Notes and tips:

• In the formula MONTH($A$2:$A$24)=F2 and YEAR($A$2:$A$24)=$E$2 are criteria that match the specified month in F2 and year in E2. Update the ranges and references (such as A2:A24, E2, F2) to suit your data layout.
• For a count by month only, ignoring the year, use:
  =SUMPRODUCT(1*(MONTH($A$2:$A$24)=F2))
• Make sure the date column contains real Excel date values, not text-formatted dates to avoid errors or mismatches. If your formula returns unexpected results, double-check date formatting.
• If your dataset is large, consider using PivotTables or VBA for performance and easier maintenance.

This method is suitable for most scenarios that require quick date statistics and when you want results to update automatically upon data modifications. However, working with multiple grouping conditions may make formulas complex and harder to maintain.

If you have Kutools for Excel installed, you can take advantage of its intuitive utilities to group and count the number of occurrences by year, month, weekday, day, or further combinations like year & month or month & day, without needing to construct complex formulas. This approach is especially efficient for users looking for a visual, menu-driven solution.

1. Select the column that contains your dates, and click Kutools > Format > Apply Date Formatting. The following dialog will appear:

2. In the Apply Date Formatting dialog box, pick the formatting style corresponding to your counting requirement (such as month, year, weekday, day, etc.), and then click OK. For instance, select "Mar" for counting by month.

3. While the date column is still selected, click Kutools > To Actual. This step converts all dates to the displayed values (e.g., month names) for easier grouping in subsequent steps.

4. Next, select the range containing your converted group names and associated data (such as Amount or Category columns). Go to Kutools > Content > Advanced Combine Rows. You'll see the following interface:

5. In the Advanced Combine Rows dialog box:
(1) Set your date column as the Primary Key to group by it.
(2) For the column you want to count (e.g., Amount), set the calculation to Count.
(3) You may choose other aggregation or combination methods for other columns (e.g., combine fruit names with a comma).
(4) Click OK to process.

Your data will now display the count of records per selected period. See screenshot below:

Compared to manual formulas, Kutools streamlines the process, reduces human error, and is highly suitable for users who frequently perform grouped counting and want to avoid formula complexity. It works well for both small and large datasets. Remember to backup your data before converting or combining rows in bulk.

PivotTables offer a powerful and interactive way to analyze large datasets and summarize occurrences by one or more time dimensions—year, month, quarter, hour, etc.—all with an easy point-and-click interface. PivotTables also allow quick reconfiguration and filtering, making them ideal for exploring data patterns or preparing management reports.

1. Select your data table, then go to Insert > PivotTable. The Create PivotTable dialog box appears.

2. In the dialog, specify where to place the PivotTable (new worksheet or an existing location such as cell E1), then click OK.

3. In the PivotTable Fields pane, drag the Date field to the Rows section and the Amount (or target field) to the Values section. By default, values may be summed.

The PivotTable appears as in the screenshot below:

4. Change the value calculation to a count by right-clicking the value column title (e.g., Sum of Amount), then choosing Summarize Values By > Count.

5. To group by additional periods (such as month, year, or quarter), right-click any cell in the Row Labels column, select Group, and from the dialog, choose the grouping criteria (such as Months, Years, or Quarters), then click OK.

Your table now displays counts by the selected period(s):

Note: Grouping by multiple periods (e.g., month and year) will add extra levels in the Row Labels. You may rearrange grouping fields (e.g., move Years below Date) in the PivotTable Fields pane to adjust your summary view.

This approach is best for large and dynamic datasets, requiring periodic grouping, comparison, and summarization. It is less suitable for quick, ad-hoc cell-level calculations or for users who are unfamiliar with PivotTable features.

When you need to repeatedly generate occurrence summaries grouped by various time periods, or wish to automate the counting process for efficiency—especially in large datasets—a custom VBA macro can be an effective solution. This method is highly suitable if you process data on a regular basis, produce periodic summary tables, or need custom groupings (such as fiscal quarters or weeks) not easily handled by formulas or PivotTables.

Complete operation steps:

• Back up your workbook before running any macro for the first time.
• Click Developer > Visual Basic to open the VBA editor.
• Click Insert > Module, then copy and paste the code below into the Module window.

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

After entering the code:

• Return to Excel and press Alt+F8, select CountOccurrencesByPeriod, and click Run.
• A prompt will ask you to select the date range to analyze. Select the relevant column or range containing your dates.
• A second prompt asks which period to group by: enter "Year", "Quarter", "Month", or "Week" (case-insensitive).
• The macro will generate a new worksheet called Occurrence_Summary listing each period and the count of occurrences within it.

Troubleshooting and Tips:

• If you encounter a macro security warning, adjust macro settings in File > Options > Trust Center > Macro Settings.
• Ensure your date column contains valid Excel date values; text strings or mixed formats may produce inaccurate counts or errors.
• The macro is flexible—enter "Quarter" to quickly group counts by year and quarter, or "Week" to summarize on a weekly basis.
• If you wish to customize the output (e.g., add more details), you can modify the macro to process additional columns or calculation rules.

This solution is robust for batch reporting or periodic analysis but presumes basic familiarity with VBA and proper workbook management. If you want to combine visual summarization, consider using both PivotTables and VBA.

Counting the frequency of entries or events by week is a common requirement in sales tracking, project management, and resource allocation. Excel provides the WEEKNUM function, which returns the week number of a given date within a year, making it easy to group data on a weekly basis using formulas.

Applicable scenario: You have a list of dates (e.g., sales or attendance data) and want to count how many entries fall into each week of the year. This method works well for ongoing analysis and when your data changes frequently, as the count updates automatically.

1. In an empty column (for example, B2), enter the following formula to calculate the week number for each date in column A:

=WEEKNUM(A2,1)

The second argument ("1") indicates weeks start on Sunday (change to "2" if you want weeks starting on Monday). Copy this formula down for all rows of your date data.

2. Make a list of week numbers you want to summarize (e.g.,1,2,3, …). In another blank cell (say, D2), use the following formula to count the occurrences for a specific week number (assuming B2:B24 lists the week numbers and D2 contains the week to search for):

=COUNTIF($B$2:$B$24, D2)

After pressing Enter, drag this formula down for your list of week numbers. Each result shows the count of occurrences for that week.

Tips and precautions:

• If you wish to count by both year and week, to distinguish entries across different years, use:

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  Copy
  Where F2 is the target year and G2 is the target week number. Adjust column ranges and references as needed.
• The WEEKNUM function's week numbering may differ depending on the setting (system, US/ISO, your chosen start day).
• If using ISO week numbers (European standard, weeks starting Monday and first week is one with first Thursday), use =ISOWEEKNUM(A2) (for Excel2013 and later).
• Always ensure all your date values are in valid Excel date format for accurate results.

This method is flexible for dynamic data tables and can be adapted for dashboards, periodic summaries, and when you wish to cross-tabulate counts by week without using PivotTables or additional add-ins.

Count the number of weekends/weekdays between two dates in Excel

Countif by date/month/year and date range in Excel

Countif filtered data/list with criteria in Excel