Combination Sumif and/or Countif on a condition

[rex759]

Hi,
I want to determine if there's a gap between dates for each week and team.
For example, Team A on week 1 has dates of 1/30 & 1/31 so there is no gap between the dates.
Team A on week 2 has dates of 2/6, 2/7 & 2/9 creating a gap between 2/7 & 2/9.
The dates in the column may not run chronological order

I thought I could used something like SUMIFs or a combination of Sumifs and Countif but not sure how to work in if a date in a range is greater than 1 day.
Below is an example with results.

Any advice/help is appreciated.

Travel.xlsx
	A	B	C	D	E	F	G	H
1	date	Week#	Team		Results
2	1/30/24	1	A			Is there a gap?
3	1/31/24	1	A		Team	Week #
4	2/1/24	1	B			1	2	3
5	2/6/24	2	A		A	NO	YES	NO
6	2/6/24	2	C		B	NO	NO	NO
7	2/7/24	2	A		C	NO	YES	YES
8	2/7/24	2	C
9	2/8/24	2	B		90643
10	2/9/24	2	A
11	2/9/24	2	B
12	2/9/24	2	C
13	2/12/24	3	C
14	2/13/24	3	C
15	2/15/24	3	C
16	2/16/24	3	C
Sheet1

Cell Formulas
Range	Formula
E9	E9	=SUMIFS(A2:A16,B2:B16,F4,C2:C16,E5)
B2:B16	B2	=INT((A2-DATE(2024,1,29))/7)+(WEEKDAY(A2)<7)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	date	Week#	Team		Results
2	30/01/2024	1	A			Is there a gap?
3	31/01/2024	1	A		Team	Week #
4	01/02/2024	1	B			1	2	3
5	06/02/2024	2	A		A	No	Yes	No
6	06/02/2024	2	C		B	No	No	No
7	07/02/2024	2	A		C	No	Yes	Yes
8	07/02/2024	2	C
9	08/02/2024	2	B
10	09/02/2024	2	A
11	09/02/2024	2	B
12	09/02/2024	2	C
13	12/02/2024	3	C
14	13/02/2024	3	C
15	15/02/2024	3	C
16	16/02/2024	3	C
Data

Cell Formulas
Range	Formula
F5:H7	F5	=LET(f,FILTER($A$2:$A$100,($B$2:$B$100=F$4)*($C$2:$C$100=$E5)),IFERROR(IF(MAX(f)-MIN(f)+1=ROWS(f),"No","Yes"),"No"))
B2:B16	B2	=INT((A2-DATE(2024,1,29))/7)+(WEEKDAY(A2)<7)

 

[rex759]

wow, that was quick! Yes, that works. Havent used the "Let" function and just started playing with Filter. Amazing! Thank you

 

[Fluff]

You're welcome & thanks for the feedback.