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.

	A	B	C	D			G	H
1	renting serial	Date out	Expected back	Actual back			ItemCode	Date expected back
2	ABC	10-Feb	12-Feb	12-Feb			ABC	1/0/1900
3	ABC	10-Apr	15-Apr	12-Apr			DEF	5/20/2019
4	ABC	2-May	5-May	6-May			GHI	1/0/1900
5	DEF	1-May	5-May	5-May			JKL	2/12/2019
6	DEF	1-May	20-May
7	GHI	8-Feb	10-Feb
8	GHI	1-Apr	10-Apr	10-Apr
9	JKL	10-Feb	12-Feb

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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