COUNTIFS formula changes to allow auto-populating when dragging down rows

Question

I am wondering if there is a way to add a +1 to the year-week data in row 1 of the equation below so I can drag it down and rows will auto-populate, save me typing in "2025-28", "2025-29", "2025-30" etc etc

Row 1 (week 27 data) =COUNTIFS(Main!$G$2:$G$184,"Surplus TBA", Main!$AR$2:$AR$184, "2025-27")

Row 2 (week 28 data) =COUNTIFS(Main!$G$2:$G$184,"Surplus TBA", Main!$AR$2:$AR$184, "2025-28")

Row 3 (week 29 data) =COUNTIFS(Main!$G$2:$G$184,"Surplus TBA", Main!$AR$2:$AR$184, "2025-29")

Row 4 (week 30 data) =COUNTIFS(Main!$G$2:$G$184,"Surplus TBA", Main!$AR$2:$AR$184, "2025-30") and so forth...

Thanks for your help!

Answer 1

Since column AR contains data in YYYY-WW format, you meed to change your COUNTIF criteria from "27-2025" to "2025-27".

To allow you to copy the formula from E4 down, use the following

=COUNTIF(Main!$AR$2:$AR$184, "2025-"&TEXT(23+ROW(),"00"))

In E4, this will count week 27 entries. In E5, will count week 28. Etc.

Answer 2

To achieve the auto-population of the week data in your COUNTIFS formula, you can use the TEXT function combined with ROW to dynamically generate the week number. Here’s how you can modify your formula:

In Row 1, you can use:

=COUNTIFS(Main!$G$2:$G$184,"Surplus TBA", Main!$AR$2:$AR$184, "2025-" & TEXT(27 + ROW() - 1, "00"))

This formula works as follows:

• ROW() returns the current row number. For Row 1, it returns 1, for Row 2 it returns 2, and so on.
• By subtracting 1 from ROW(), you adjust the starting point to 0, so when you add it to 27, it will start from week 27 and increment as you drag the formula down.
• The TEXT(..., "00") function formats the week number to always have two digits.

When you drag this formula down to Row 2, it will automatically adjust to week 28, and so forth, allowing you to avoid manually typing each week number.

Answer 3

Dear @Office worker,

Thank you for posting your question in the Microsoft Q&A forum!

Based your available information you've provided, I understand that you want to streamline the use of the COUNTIFS formula in Excel by dynamically adjusting the year-week data as you drag down the rows.

In this case, please try these formulas and check if it works:

In column A, generate the week labels:

="2025-" & TEXT(26 + ROWS($A$1:A1), "00")

In column B (your counts), reference that label:

=COUNTIFS(Main!$G$2:$G$184,"Surplus TBA",Main!$AR$2:$AR$184,A1)

This separates concerns: column A creates "2025-27", "2025-28", …; column B counts for whatever is in A.

Note: Please note that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.   

Please kindly correct me if I am misunderstanding. Your understanding and co-operation are highly appreciated. I am looking forward to your response!

---

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 4

Nice to finally see the following added to some posts: "AI generated content. This question contains an answer created with AI from Q&A Assist."

Not sure how this is finally working since "many" do not recognize these AI generated posts.

Anyway, whatever your layout is, I would use regular dates. When you need to include a weeknumber in your formula, use the function ISOWEEKNUM( )

As you know, ROW() won't work after row 52.