Count number of weeks of submitted business

[AT_TreeFortConsulting]

I'm trying to use a formula to count the number of weeks a client has submitted new business during a quarter. The data is in a table, and submitted business includes a date column. Is there a way to count unique weeks based on the condition that an entry equals "New Business Submit"?

Table is shown below:

Date	Goal	Qtr	Day	Wk
10/12/21	New Clients	4	3	42
10/18/21	Submit New Business	4	2	43
10/18/21	Submit New Business	4	2	43
11/02/21	New Clients	4	3	45
11/03/21	Submit New Business	4	4	45
11/03/21	Submit New Business	4	4	45
11/11/21	Submit New Business	4	5	46
11/11/21	New Clients	4	5	46

 

[Sufiyan97]

Like this?

Book2
	A	B	C	D	E	F	G	H
1	Date	Goal	Qtr	Day	Wk	Week	Count
2	10/12/2021	New Clients	4	3	42	42
3	10/18/2021	Submit New Business	4	2	43	43	2
4	10/18/2021	Submit New Business	4	2	43	43	2
5	11/2/2021	New Clients	4	3	45	45
6	11/3/2021	Submit New Business	4	4	45	45	2
7	11/3/2021	Submit New Business	4	4	45	45	2
8	11/11/2021	Submit New Business	4	5	46	46	1
9	11/11/2021	New Clients	4	5	46	46
10
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=WEEKNUM(A2)
G2:G9	G2	=IF(B2="Submit New Business",COUNTIFS(C:C,C2,B:B,"Submit New Business",F:F,F2),"")

 

[AT_TreeFortConsulting]

Like this?

Book2
	A	B	C	D	E	F	G	H
1	Date	Goal	Qtr	Day	Wk	Week	Count
2	10/12/2021	New Clients	4	3	42	42
3	10/18/2021	Submit New Business	4	2	43	43	2
4	10/18/2021	Submit New Business	4	2	43	43	2
5	11/2/2021	New Clients	4	3	45	45
6	11/3/2021	Submit New Business	4	4	45	45	2
7	11/3/2021	Submit New Business	4	4	45	45	2
8	11/11/2021	Submit New Business	4	5	46	46	1
9	11/11/2021	New Clients	4	5	46	46
10
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=WEEKNUM(A2)
G2:G9	G2	=IF(B2="Submit New Business",COUNTIFS(C:C,C2,B:B,"Submit New Business",F:F,F2),"")

I'm looking to count the number of weeks where at least one piece of new business was submitted. So in the chart I included, the total would be 3. I hope that makes sense?

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H
1	Date	Goal	Qtr	Day	Wk	Week	Count
2	10/12/2021	New Clients	4	3	42	42
3	10/18/2021	Submit New Business	4	2	43	43	1
4	10/18/2021	Submit New Business	4	2	43	43
5	11/2/2021	New Clients	4	3	45	45
6	11/3/2021	Submit New Business	4	4	45	45	1
7	11/3/2021	Submit New Business	4	4	45	45
8	11/11/2021	Submit New Business	4	5	46	46	1
9	11/11/2021	New Clients	4	5	46	46
10
11					Total Count		3
12
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=WEEKNUM(A2)
G2:G9	G2	=IF(IF(B2="Submit New Business",COUNTIFS($B$2:B2,"Submit New Business",$F$2:F2,F2),"")>1,"",COUNTIFS($B$2:B2,"Submit New Business",$F$2:F2,F2))
G11	G11	=SUM(G2:G9)

 

[AT_TreeFortConsulting]

Yes that worked. Thank you!

 

[Sufiyan97]

You're Welcome!