How to Countif for the current week

[Mista_sav]

Hi Team,

i have the following formula that counts dates if they are todays date

=COUNTIF('5 Days Post Settlement'!D11:D1999,TODAY())

How can i change this so it counts the dates in the current week instead (Mon-Sun)?

Technically it is pulling from multiple sheets and this is the full formula, i just used the above section to make it less clunky haha

=COUNTIF('5 Days Post Settlement'!D11:D1999,TODAY())+COUNTIF('6 Weeks Post Settlement'!D11:D1999,TODAY())+COUNTIF('3 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('6 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('12 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('18 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('24 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('30 Months Post Settlement'!D11:D1999,TODAY())+COUNTIF('36 Months Post Settlement'!D11:D1999,TODAY())

 

[bebo021999]

For single sheet

=SUMPRODUCT(COUNTIF('5 Days Post Settlement'!D11:D1999,TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,-1,-2,-3,-4,-5)+{0,1,2,3,4,5,6}))

For multiple sheet, use this formula, but adjust the sheet name. But it is not a good choice, with such long formula.
Using VBA, with a button instead.

 

[Mista_sav]

For single sheet

=SUMPRODUCT(COUNTIF('5 Days Post Settlement'!D11:D1999,TODAY()+CHOOSE(WEEKDAY(TODAY()),1,0,-1,-2,-3,-4,-5)+{0,1,2,3,4,5,6}))

For multiple sheet, use this formula, but adjust the sheet name. But it is not a good choice, with such long formula.
Using VBA, with a button instead.

 

[Mista_sav]

Im not skilled with writing the code for VBA Sorry. I have input them though form pre written codes. Thanks again mate

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have recent version with the LET function, try

=LET(r,'5 Days Post Settlement'!D11:D1999,t,TODAY(),COUNTIFS(r,">"&t-WEEKDAY(t,2),r,"<"&t+8-WEEKDAY(t,2)))

Otherwise try

=COUNTIFS('5 Days Post Settlement'!D11:D1999,">"&TODAY()-WEEKDAY(TODAY(),2),'5 Days Post Settlement'!D11:D1999,"<"&TODAY()+8-WEEKDAY(TODAY(),2))