COUNTIF For the Current Week

[Mista_sav]

Not sure why im finding this so difficult but im wondering if anyone can help.

I have one sheet that from columns E through to P are all dates. Some are in the future and what i need is a formula that will count all dates in the current working week.

This is about as far as i got. I tried a few tips online but could not for te life of me get it to work

=COUNTIFS('Call List'!E11:P30

Thank you

 

[Mista_sav]

How about

=SUMPRODUCT(--(WEEKNUM(IF(E11:P30="",0,E11:P30))=WEEKNUM(TODAY())))

Thank you, works perfect!!

 

[Mista_sav]

I tell a lie sorry haha. It worked ok first test then wen i tried it again i got this below I changed the formula you gave to cover the whole table range. But now its counting 6 when it should be 2 (Only 2 calls due this current week?) =SUMPRODUCT(--(WEEKNUM(IF(Table1[[Follow Up call Due]:[36 Months]]="",0,Table1[[Follow Up call Due]:[36 Months]]))=WEEKNUM(TODAY())))

Post Approval/Settlement Call List
All Calls Highlighted below are calls due to be made in the current Week. Calls due must be input in the Call Diary
Calls Due This Week				6
Member #	Name	KO Number		Follow Up call Due	Post Aproval Call		5 days Post	6 Weeks	3 Months	6 Months	12 Months	18 Months	24 Months	30 Months	36 Months
123456	Test 1	987354			Saturday, 28 January 2023		Saturday, 28 January 2023	Monday, 6 March 2023	Sunday, 23 April 2023	Sunday, 23 July 2023	Tuesday, 23 January 2024	Tuesday, 23 July 2024	Thursday, 23 January 2025	Wednesday, 23 July 2025	Friday, 23 January 2026
742753	Test 2	165165164
35721	Test 3	161616
37433	Test 4	13216161
453453	Test 5	1321651566
45345342	Test 6	51516262
4496845	Test 7	653265
13313	Test 8	313641849
431234	Test 9	3264946
4532313	Test 10	232649
77888	Test 11	32356366
9966	Test 12	22564646
6633	Test 13	3165444
5545	Test 14	659659569
5456	Test 15	326565
23968	Test 16	262332632
22264	Test 17	2665262
232656	Test 18	265265262

 

[Fluff]

Can you post the entire data (no hidden columns) & use the minisheet option, not the table option.

 

[Mista_sav]

Apologies, i made some changes. I hid some columns as i dont want it to count columns D and E now. I need Columns F through to P to count if its the current week. This data pulls automatically from another sheet where the user inputs dates. This sheets then automates future dated calls

Post Approval & Settlement Call Tracking - WIP NEW STYLE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Post Approval/Settlement Call List
2
3	All Calls Highlighted below are calls due to be made in the current Week. Calls due must be input in the Call Diary
4
5	Calls Due This Week			5
6
7
8
9
10	Member #	Name	KO Number	Approval Date	Settlement Date	Follow Up call Due	Post Aproval Call	5 days Post	6 Weeks	3 Months	6 Months	12 Months	18 Months	24 Months	30 Months	36 Months
11	123456	Test 1	987354	Friday, 27 January 2023	Monday, 23 January 2023		Saturday, 28 January 2023	Saturday, 28 January 2023	Monday, 6 March 2023	Sunday, 23 April 2023	Sunday, 23 July 2023	Tuesday, 23 January 2024	Tuesday, 23 July 2024	Thursday, 23 January 2025	Wednesday, 23 July 2025	Friday, 23 January 2026
12	742753	Test 2	165165164
13	35721	Test 3	161616
14	37433	Test 4	13216161
15	453453	Test 5	1321651566
16	45345342	Test 6	51516262
17	4496845	Test 7	653265
18	13313	Test 8	313641849
19	431234	Test 9	3264946
20	4532313	Test 10	232649
21	77888	Test 11	32356366
22	9966	Test 12	22564646
23	6633	Test 13	3165444
24	5545	Test 14	659659569
25	5456	Test 15	326565
26	23968	Test 16	262332632
27	22264	Test 17	2665262
28	232656	Test 18	265265262
Call List

Cell Formulas
Range	Formula
D5	D5	=SUMPRODUCT(--(WEEKNUM(IF(F11:P3000="",0,F11:P3000))=WEEKNUM(TODAY())))
A11:A28	A11	=IF('Input Data & Dates'!$A11="","",'Input Data & Dates'!$A11)
B11:B28	B11	=IF('Input Data & Dates'!$B11="","",'Input Data & Dates'!$B11)
C11:C28	C11	=IF('Input Data & Dates'!$C11="","",'Input Data & Dates'!$C11)
D11:D28	D11	=IF('Input Data & Dates'!$D11="","",'Input Data & Dates'!$D11)
E11:E28	E11	=IF('Input Data & Dates'!$E11="","",'Input Data & Dates'!$E11)
F11:F28	F11	=IF('Input Data & Dates'!$F11="","",'Input Data & Dates'!$F11)
G11:G28	G11	=IF($D11="","",$D11+1)
H11:H28	H11	=IF($E11="","",$E11+5)
I11:I28	I11	=IF($E11="","",$E11+7*6)
J11:J28	J11	=IF($E11="","",EDATE($E11,3))
K11:K28	K11	=IF($E11="","",EDATE($E11,6))
L11:L28	L11	=IF($E11="","",EDATE($E11,12))
M11:M28	M11	=IF($E11="","",EDATE($E11,18))
N11:N28	N11	=IF($E11="","",EDATE($E11,24))
O11:O28	O11	=IF($E11="","",EDATE($E11,30))
P11:P28	P11	=IF($E11="","",EDATE($E11,36))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:P3443	Dates Occurring	this week	text	NO

 

[Mista_sav]

Oh, i forgot to specify; COUNT if current week in current year

 

[Fluff]

Ok you have other years in there, try

=SUMPRODUCT((YEAR(IF(F11:P3000="",0,F11:P3000))=YEAR(TODAY()))*(WEEKNUM(IF(F11:P3000="",0,F11:P3000))=WEEKNUM(TODAY())))

 

[Mista_sav]

Ok you have other years in there, try

=SUMPRODUCT((YEAR(IF(F11:P3000="",0,F11:P3000))=YEAR(TODAY()))*(WEEKNUM(IF(F11:P3000="",0,F11:P3000))=WEEKNUM(TODAY())))

Perfect! Like a charm.
Thanks mate again for your help

 

[Fluff]

You're welcome & thanks for the feedback.