Countifs function for weekly reporting over several years

Question

Hello Excel Community :)

I am building a data tracker to record data on cases, and these data need to be reported on a weekly basis. For example:

In order to report data by week, I convert the date a case is received into the week of the year (e.g. 30 June 2025 = week 27 of 2025) and, as shown above, count the relevant number of cases per week.

However, if we keep tracking and recording data for more than 1 year, this system will not work as numbers will double up, as the weeks of the following year will have the same week number labels.

1. What would be the easiest solution so there is no doubling up of year week numbers?
2. Is there a better way to report data on a weekly basis?

Thank you!

Answer 1

Dear @Office worker,

Thank you for posting your question in the Microsoft Q&A forum! I understand you’re building a weekly case tracker and want to avoid duplicate week numbers when reporting across multiple years. 

Using only WEEKNUM or hardcoded week numbers will cause duplication because weeks repeat each year. 

Include both Year and Week Number in your calculation. For example, create a helper column with this formula: 

=YEAR(B2)& "-" & WEEKNUM(B2,2)   

Where A2 is your date. This will return values like 2025-27, ensuring each week is unique across years. 

You can then update your COUNTIFS formulas to reference this combined Year-Week value instead of just the week number. 

Alternative Approach: 

Consider using PivotTables with the date field grouped by Year and Week. This avoids manual formulas and provides dynamic reporting. 

For more details, please refer to these official resources: 

• COUNTIFS function - Microsoft Support 
• WEEKNUM function - Microsoft Support 

If you want us to provide more clarity on the issue, please send us your excel sample, we will be happy to give you more detailed instructions directly.

Please let me know if you’d like step-by-step instructions for setting up the helper column or a PivotTable solution. 

---

 If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Dear @Office worker,

Thank you for your kind reply! If you want to create a unique identifier like 2025-27 directly from the original date (e.g., cell I2 = Thursday, 3 July 2025) without intermediate steps, you can use the following formula:

=TEXT(I2,"YYYY") & "-" & TEXT(WEEKNUM(I2,2),"00")

Explanation:

• TEXT(I2,"YYYY") → Extracts the year from the date in cell I2.
• WEEKNUM(I2,2) → Returns the week number, with Monday as the start of the week.
• TEXT(...,"00") → Formats the week number as two digits (e.g., 01, 27).
• & "-" & → Combines them into YYYY-WW format.

For example: If I2 = 3 July 2025, the formula will return:

This combined Year-Week value can then be used in your COUNTIFS formulas or as a helper column to ensure unique weekly reporting across multiple years.

Answer 3

Hi Sophie,

That's a great plan, thank you so much.

Apologies as I did not explain that the image shows the weekly report, rather than the untransformed date.

I would like to know how to implement the equation you have described from the main data page using the initial (untransformed) date?

For e.g.

Cell I2 = Thursday 3 July 2025

This is converted to week 27 using the formula =WEEKNUM(I2,2).

So ideally, I'd like to implement your formula on cell I2 to obtain 2025-27?