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

David0227

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

Service IDDateMax Date ServiceMax Date Service ValueOverall Completion Date
11/24/991/25/991/25/99
11/24/991/25/991/25/99
11/25/991/25/9911/25/99
11/26/991/25/99
22/24/982/23/98
52/27/982/28/9829/02/1998
52/28/982/28/9812/28/98
63/1/983/1/9813/1/98
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
How about

+Fluff New.xlsm
ABCDE
1Service IDDateMax Date ServiceMax Date Service ValueOverall Completion Date
2124/01/199925/01/1999 25/01/1999
3124/01/199925/01/1999 25/01/1999
4125/01/199925/01/1999125/01/1999
5126/01/1999  25/01/1999
6224/02/1998  23/02/1998
7527/02/199828/02/1998 29/02/1998
8528/02/199828/02/1998128/02/1998
9601/03/199801/03/1998101/03/1998
Raw
Cell Formulas
RangeFormula
C2:C9C2=IF(B2>E2,"",MAX(IF(($A$2:$A$9=A2)*($B$2:$B$9<=$E$2:$E$9),$B$2:$B$9)))
D2:D9D2=IF(AND(B2=C2,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2)),1,"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,232
Messages
5,546,643
Members
410,752
Latest member
MC01_
Top