MAX date, item match and empty cells

[reiroch171]

Hello Board,

Here is my dilemma.
I am trying to populate column H with the MAX date from column C where:

1. item code matches (A=G)
2. D is blank

If no items in D are blank, make zero. (I will conditional format so it's blank and not 1/0/1900 if I need to)

Note, this table A-D will NOT be sorted at all, so I don't think lookup will work?

I searched the forums and came up with the parts - sumproduct, max(if(etc)) but I can't get the whole thing to work.


[TABLE="width: 841"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]renting serial
[/TD]
[TD="align: center"]Date out[/TD]
[TD="align: center"]Expected back[/TD]
[TD="align: center"]Actual back[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: center"]ItemCode
[/TD]
[TD="align: center"]Date expected back[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ABC
[/TD]
[TD="align: right"]10-Feb[/TD]
[TD="align: right"]12-Feb[/TD]
[TD="align: right"]12-Feb[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]ABC[/TD]
[TD="align: right"]1/0/1900
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]ABC
[/TD]
[TD="align: right"]10-Apr[/TD]
[TD="align: right"]15-Apr[/TD]
[TD="align: right"]12-Apr[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DEF[/TD]
[TD="align: right"]5/20/2019[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ABC[/TD]
[TD="align: right"]2-May[/TD]
[TD="align: right"]5-May[/TD]
[TD="align: right"]6-May[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]GHI[/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]DEF[/TD]
[TD="align: right"]1-May[/TD]
[TD="align: right"]5-May[/TD]
[TD="align: right"]5-May[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]JKL[/TD]
[TD="align: right"]2/12/2019[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]DEF[/TD]
[TD="align: right"]1-May[/TD]
[TD="align: right"]20-May[/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]GHI[/TD]
[TD="align: right"]8-Feb[/TD]
[TD="align: right"]10-Feb[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]GHI
[/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"]10-Apr[/TD]
[TD="align: right"]10-Apr[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]JKL[/TD]
[TD="align: right"]10-Feb[/TD]
[TD="align: right"]12-Feb[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

What I tried:

Example: =SUMPRODUCT((Table13[renting serial]=$H2)*(Table13[Actual back]=0)) gets me just the count of lines
=MAX(IF(Table13[renting serial]=H2,Table13[Expected back])) gets me just the max date in the whole column.

Thanks so much,
Susan

 

[Eric W]

Welcome to the Board.

Try this formula in H2:

=MAX(IF($A$2:$A$9=G2,IF($D$2:$D$9="",$C$2:$C$9)))
and confirm it with Control+Shift+Enter. Drag down the column.

If you have Excel 365, check out the MAXIFS function.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	renting serial	Date out	Expected back	Actual back			ItemCode	Date expected back
2	ABC	10-Feb	12/02/2019	12-Feb			ABC	00/01/1900
3	ABC	10-Apr	15/04/2019	12-Apr			DEF	20/05/2019
4	ABC	02-May	05/05/2019	06-May			GHI	10/02/2019
5	DEF	01-May	05/05/2019	05-May			JKL	12/02/2019
6	DEF	01-May	20/05/2019
7	GHI	08-Feb	10/02/2019
8	GHI	01-Apr	10/04/2019	10-Apr
9	JKL	10-Feb	12/02/2019
Doc1

Cell Formulas
Range	Formula
H2	{=MAX(IF((Table13[renting serial]=G2)*(Table13[Actual back]=""),Table13[Expected back]))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[reiroch171]

Thanks that worked great.

Ps I was a user here like 8 years ago from my old job - can't remember the login though, so I started fresh

 

[Fluff]

Glad we could help & thanks for the feedback