David0227
New Member
- Joined
- Mar 1, 2020
- Messages
- 14
- Office Version
- 2011
- 2010
- 2007
- Platform
- Windows
- MacOS
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,"")
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 |