Customer Count per Week

[joand]

I have a table with columns: Team, Date, Customer, Task Description, Time, and Week Number. I want to count the number of instances a customer appears on a single week. My issue is that there are instances wherein a customer appears multiple times on a given day. It should count only as 1 per day. For example, in the table below, the total count for customer 1A for Week 27 should only be 1 not 2 for Team A. For customer 1B, the total count should be 2 for Team A since 7/3 and 7/4 are different dates and they belong to week 27. I need the customer count per week per team. Any ideas?

Team	Date	Customer	Task Description	Time	WkNum
A	07/03/2023	1A	XXX	3	27
A	07/03/2023	1A	XXX	4	27
A	07/03/2023	1B	XXX	4	27
A	07/04/2023	1B	XXX	2	27
A	07/04/2023	2A	XXX	4	27
B	07/03/2023	2A	XXX	1	27
B	07/04/2023	2A	XXX	3	27
B	07/04/2023	2A	XXX	1	27
C	07/05/2023	2B	XXX	4	27
C	07/05/2023	2B	XXX	2	27
C	07/05/2023	2B	XXX	1	27
C	07/05/2023	2B	XXX	2	27

 

[SanjayGMusafir]

Check this -

Book1
	A	B	C	D	E	F	G	H
1	Team	Date	Customer	Task Description	Time	WkNum	Count	Count Wk
2	A	7/3/23	1A	XXX	3	27	1	1
3	A	7/3/23	1A	XXX	4	27	0	1
4	A	7/3/23	1B	XXX	4	27	1	2
5	B	7/3/23	2A	XXX	1	27	1	2
6	A	7/4/23	1B	XXX	2	27	1	2
7	A	7/4/23	2A	XXX	4	27	1	2
8	B	7/4/23	2A	XXX	3	27	0	2
9	B	7/4/23	2A	XXX	1	27	0	2
10	C	7/5/23	2B	XXX	4	27	1	1
11	C	7/5/23	2B	XXX	2	27	0	1
12	C	7/5/23	2B	XXX	1	27	0	1
13	C	7/5/23	2B	XXX	2	27	0	1
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=IFS(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)>1,0,TRUE,COUNTIFS($B$2:B2,B2,$C$2:C2,C2))
H2:H13	H2	=SUMIFS($G$2:$G$13,$C$2:$C$13,C2,$F$2:$F$13,F2)

 

[joand]

Hi, could you include Team in the parameter?

 

[SanjayGMusafir]

Hi, could you include Team in the parameter?

Try it and revert -

Book2
	A	B	C	D	E	F	G	H
1	Team	Date	Customer	Task Description	Time	WkNum	Count	Count Wk
2	A	45110	1A	XXX	3	27	1	1
3	A	45110	1A	XXX	4	27	0	1
4	A	45110	1B	XXX	4	27	1	2
5	B	45110	2A	XXX	1	27	1	2
6	A	45111	1B	XXX	2	27	1	2
7	A	45111	2A	XXX	4	27	1	1
8	B	45111	2A	XXX	3	27	1	2
9	B	45111	2A	XXX	1	27	0	2
10	C	45112	2B	XXX	4	27	1	1
11	C	45112	2B	XXX	2	27	0	1
12	C	45112	2B	XXX	1	27	0	1
13	C	45112	2B	XXX	2	27	0	1
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=IFS(COUNTIFS($B$2:B2,B2,$C$2:C2,C2,$A$2:A2,A2)>1,0,TRUE,COUNTIFS($B$2:B2,B2,$C$2:C2,C2,$A$2:A2,A2))
H2:H13	H2	=SUMIFS($G$2:$G$13,$C$2:$C$13,C2,$F$2:$F$13,F2,$A$2:$A$13,A2)

 

[joand]

Thank you for your help!

 

[SanjayGMusafir]

Glad to help you and thanks for the update