Return MAX Date and value of 1 based on the MAX Date being after an overall date

[David0227]

I've kindly been provided the fomulas for column C and D below, column C returns the max date per service from column B, and column D returns a 1 value per service even if the services have the same date in column B

I'd like to add an additional layer that only returns column C and column D's output if the value in Column B is before or equal to the date in column E

Column C = =MAX(IF($A$2:$A$9=A2,$B$2:$B$9))
Column D =IF(AND(B2=C2,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2)),1,"")

Service ID	Date	Max Date Service	Max Date Service Value	Overall Completion Date
1	1/24/99	1/25/99		1/25/99
1	1/24/99	1/25/99		1/25/99
1	1/25/99	1/25/99	1	1/25/99
1	1/26/99			1/25/99
2	2/24/98			2/23/98
5	2/27/98	2/28/98		29/02/1998
5	2/28/98	2/28/98	1	2/28/98
6	3/1/98	3/1/98	1	3/1/98

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E
1	Service ID	Date	Max Date Service	Max Date Service Value	Overall Completion Date
2	1	24/01/1999	25/01/1999		25/01/1999
3	1	24/01/1999	25/01/1999		25/01/1999
4	1	25/01/1999	25/01/1999	1	25/01/1999
5	1	26/01/1999			25/01/1999
6	2	24/02/1998			23/02/1998
7	5	27/02/1998	28/02/1998		29/02/1998
8	5	28/02/1998	28/02/1998	1	28/02/1998
9	6	01/03/1998	01/03/1998	1	01/03/1998
Raw

Cell Formulas
Range	Formula
C2:C9	C2	=IF(B2>E2,"",MAX(IF(($A$2:$A$9=A2)*($B$2:$B$9<=$E$2:$E$9),$B$2:$B$9)))
D2:D9	D2	=IF(AND(B2=C2,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2)),1,"")

 

[David0227]

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.