Count Pending Tasks by Month in an Excel Calendar
Question details
Count rows for each task and display the matching monthly total in an Excel calendar.
- Product
- Microsoft Excel calendar worksheet
- Device & OS
- not provided
- Scenario
- The user has task data with descriptions and completion dates and wants each calendar month to show the count for the corresponding task.
- Observed behavior
- The monthly calendar needs a formula that matches the task name and limits the date count to the month shown in the calendar header.
Format the source task range as an Excel table named Data, with Description and Completed Date columns. Make sure each calendar month header contains a real date value rather than text, even if it is formatted to display only the month name.
Use COUNTIFS with monthly date boundaries
Use this method when the calendar row contains a task name and each month header contains a valid date for that month.
COUNTIFS can match the task description while counting only dates after the previous month-end and on or before the current month-end. This prevents dates from adjacent months from being included.
Select the source task range, then choose Insert > Table in Excel. On the Table Design tab, set the table name to Data and confirm that the columns are named Description and Completed Date.
Enter a real date in the calendar header cell, such as 1/1/2026 in C5, then use Home > Number Format to display it as mmmm or mmm. Do not type a month name as plain text.
In the calendar cell for the task row and month column, enter =COUNTIFS(Data[Description],[@[Work Order Task]],Data[Completed Date],">"&EOMONTH(C$5,-1),Data[Completed Date],"<="&EOMONTH(C$5,0)). Replace C$5 with the month-header reference if your header is in another cell.
Use the fill handle to drag the formula across the remaining month columns. Keep the month row fixed with $ and allow the column reference to change for each month.
Correct task names and date values when the result is zero
Use this check when a month should contain tasks but COUNTIFS returns 0.
Create Monthly Task Totals in a Calendar with WPS Spreadsheets
WPS Spreadsheets lets you organize task records in a table and use Excel-compatible COUNTIFS and EOMONTH formulas to calculate monthly totals directly in your calendar layout.
- 1. Open the calendar workbook: Open the Excel calendar file in WPS Spreadsheets and select the worksheet containing the task data and monthly calendar.
- 2. Format task data as a table: Select the task range, choose Insert > Table, and name the table Data with Description and Completed Date columns.
- 3. Set date-based month headers: Enter the first day of each month in the calendar header cells, then choose Home > Number Format to display the month name.
- 4. Add the COUNTIFS formula: Click the first calendar total cell and enter the COUNTIFS formula using the task cell and month-header cell references.
- 5. Copy totals across months: Drag the fill handle across the calendar month columns to calculate the totals for every displayed month.
Frequently Asked Questions
Why does my COUNTIFS formula return zero for every calendar month?
Check that Data[Completed Date] contains real Excel dates and that the month header cell, such as C5, also contains a real date. Text dates and text month names cannot be evaluated correctly by EOMONTH.
Can I show January as text while still using it in the formula?
Yes. Enter an actual date such as 1/1/2026 in the header cell, then apply a custom or built-in month format such as mmmm. The cell will display January while the formula uses the underlying date.
What does [@[Work Order Task]] mean in the formula?
It is an Excel table structured reference. It means the Work Order Task value in the current calendar table row. If your calendar is not a table, replace it with a normal cell reference such as $A8.
How do I count tasks for a different date field instead of Completed Date?
Replace Data[Completed Date] in both date criteria with the relevant table column, such as Data[Due Date] or Data[Created Date], while keeping the same EOMONTH boundaries.
Will the formula count dates on the last day of the month?
Yes. The <=EOMONTH(C$5,0) criterion includes dates on the final day of the month, while the >EOMONTH(C$5,-1) criterion starts immediately after the prior month ends.
