COUNTIFS / WEEKNUM

[surkdidat]

HI

I have the following formula

=COUNTIFS('[Tracker.xlsm]Master List'!$J:$J,"COMPLETE",'[Tracker.xlsm]Master List'!$L:$L,">="&TODAY())

which works fine to give me the count of occurrences that happened today.

How do I amend it to do this week please (starting on a Monday)?

I have tried

=COUNTIFS('[Tracker.xlsm]Master List'!$J:$J,"COMPLETE",'[Tracker.xlsm]Master List'!$L:$L,">="&WEEKNUM(TODAY()))

but this counts over 1500 occurrences, when it only should be 6!

 

[Joe4]

Does your data span multiple years of dates?
If so, you will need to check the year in addition to the week number.

 

[surkdidat]

AH, this

Does your data span multiple years of dates?
If so, you will need to check the year in addition to the week number.

may be the case! So, how would I need to amend that please for the current year/.

 

[Joe4]

Can you post a small example of your data, along with your expected results (and explanation of exactly what you are trying to count, so we make sure we write the criteria according to what you need)?

 

[surkdidat]

(In this scenario I am taking today to be Tuesday 28 September, so to differentiate between todays count and weeks count. The week should start on a Monday

This coding works fine for todays count : -

=COUNTIFS('[Tracker.xlsm]Master List'!$J:$J,"COMPLETE",'[Tracker.xlsm]Master List'!$L:$L,">="&TODAY())

Column J	Column K	Column L	COUNT FOR TODAY?	COUNT FOR WEEK?
CURRENT STATUS	NOTES	UPDATED	-	-
COMPLETED	bsguihiu	27/9/21	NO	YES
COMPLETED	bdhygdyu	26/9/21	NO	NO
COMPLETED	huhig	28/9/21	YES	YES
ACTIVE	hihguigui	28/9/21	NO	NO
ACTIVE	hujughiug	27/9/21	NO	NO
COMPLETED	hugiugigi	28/9/21	YES	YES
			2	3
			(TAKING TODAY IN EXAMPLE AS TUESDAY 28 SEPT)

 

[Joe4]

OK, in order to use WEEKNUM in our COUNTIFS, we are going to need to use a "helper" column.
So, if we use column O to return the weeknumber, here is the formula we could use for cell O3 to return the weeknumber:

=WEEKNUM(L3,2)

and copy down for all rows.

Likewise, we should add another helper column (column P) to return the year. That formula would look like this:

=YEAR(L3)

and copy down for all rows.

Then we could get our weekly count like this (to return for the current week/year):

=COUNTIFS([Tracker.xlsm]Master List'!J:J,"COMPLETE",[Tracker.xlsm]Master List'!O:O,WEEKNUM(TODAY(),2),[Tracker.xlsm]Master List'!P:P,YEAR(TODAY()))

 

[surkdidat]

OK, in order to use WEEKNUM in our COUNTIFS, we are going to need to use a "helper" column.
So, if we use column O to return the weeknumber, here is the formula we could use for cell O3 to return the weeknumber:

=WEEKNUM(L3,2)

and copy down for all rows.

Likewise, we should add another helper column (column P) to return the year. That formula would look like this:

=YEAR(L3)

and copy down for all rows.

Then we could get our weekly count like this (to return for the current week/year):

=COUNTIFS([Tracker.xlsm]Master List'!J:J,"COMPLETE",[Tracker.xlsm]Master List'!O:O,WEEKNUM(TODAY(),2),[Tracker.xlsm]Master List'!P:P,YEAR(TODAY()))

Thank you for this. Unfortunately, the "Master Tracker Sheet" is locked/password protected, and I cannot edit/add anything to it,,, I am now wondering if the only work around is to have the week start and finish dates in a different tab, and then usingsome sort of formula around todays date being between two dates or something... Thank you anyway!

 

[Joe4]

Unfortunately, the WEEKNUM function is a scalar function, so cannot used directly in SUMPRODUCT or COUNTIFS formulas.

If you are able to put the current week starting and dates somewhere on the same sheet where you are returning this count, I think you can do what you want.
So, if in cell C1 we had the week starting date, and in cell D1, we had the week ending date, you could use this formula to return your weekly count:

=COUNTIFS([Tracker.xlsm]Master List'!J:J,"COMPLETE",[Tracker.xlsm]Master List'!L:L,">=" & C1,[Tracker.xlsm]Master List'!L:L,"<=" & D1)

And you can dynamically calculate the start of the current work week (the value for cell C1) like this:

=TODAY()-WEEKDAY(TODAY(),2)+1

and then D1 would just be:

=C1+6