Get the latest date but if there's blank the value should be blank

[anneb_87]

Hello World!

Can some help me?
How can I get the latest date on the following data?But if there's a blank date it should be blank.
I did the MAX IF for the latest date, I just don't know what to do on the condition if there's a blank date.

I would really appreciate your help!

Sample data:
client data received
client A 6/1/2018
client A 6/30/2018
client A 6/15/2018
client A
client A 6/5/2018
client B 6/20/2018
client B 6/12/2018
client B 6/1/2018
client B 6/5/2018

Expected output should be:
client data received
Client A
Client B 6/20/2018

 

[jtakw]

Hi,

If as long as there's any Blank Date field for a Client, use Blank as result...

I don't have the MAXIF function, so insert your current MAXIF within my formula where indicated:

Book1
	A	B
1	client	data received
2	client A	6/1/2018
3	client A	6/30/2018
4	client A	6/15/2018
5	client A
6	client A	6/5/2018
7	client B	6/20/2018
8	client B	6/12/2018
9	client B	6/1/2018
10	client B	6/5/2018
11
12	client	data received
13	Client A
14	Client B
Sheet82

Cell Formulas
Range	Formula
B13	=IF(COUNTIFS(A$2:A$10,A13,B$2:B$10,"")>0,"","Your MAXIF formula here")

 

[anneb_87]

Thank You @jtakw for your response!
I tried what you said and it did go well for the client A. But as I tried to copy the formula for client B. It didn't work.

Here's my formula:
Client A
{=IF(COUNTIFS($A$2:$A$10,A13,$B$2:$B$10,"")>0,"","MAX(IF($A$2:$A$20=A13,$B$2:B$2:$B$10))")}

Client B
{=IF(COUNTIFS($A$2:$A$10,A14,$B$2:$B$10,"")>0,"","MAX(IF($A$2:$A$20=A14,$B$2:B$2:$B$10))")}

Additional, when I copied and pasted the formula of Client A to Client B. I manually change the A14 from A13 in the MAXIF function. It didn't change automatically.

Do you have a different approach so I can get the latest date with the condition of blanks? Because I have like a hundreds of client to track. ???

 

[btorrean]

@anneb_87,

Do you have the curly braces {} around the formula in your workbook, as you have listed above? If you do, you're turning the formula into an array formula, which could be altering the functionality.

Brian

 

[Aladin Akyurek]

Option 1. If MAXIFS is available on your Excel system, in B13 just enter and copy down:

=IF(COUNTIFS($A$2:$A$10,$A13,$B$2:$B$10,"="),"",MAXIFS($B$2:$B$10,$A$2:$A$10,$A13))

Option 2. With AGGREGATE, if your Excel version contains this function, in B13 just enter and copy down:

=IF(COUNTIFS($A$2:$A$10,$A13,$B$2:$B$10,"="),"",MAXIFS($B$2:$B$10,$A$2:$A$10,$A13))


Option 3. Otherwise, in B13 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($A$2:$A$10,$A13,$B$2:$B$10,"="),"",MAX(IF($A$2:$A$10=$A13,$B$2:$B$10)))

Control+shift+enter: Press down the control and the shift keys at the same time while you het the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[anneb_87]

@aladinakyurek
I don't have MAXIFS function. I tried the Aggregate function but it is only working for client A as well. As I copy the formula to Client B, I am having an error of #VALUE !.

WHAT SHOULD I DO? ???

 

[anneb_87]

@btorrean
Even without the braces I still not working for Client B. ??

 

[anneb_87]

@btorrean
Even without the braces It is still not working for Client B. ??

 

[anneb_87]

@Aladin Akyurek
I have notice that the formula you have given for MAXIFS and AGGREGATE are the same. What should be the correct formula for AGGREGATE?

 

[anneb_87]

I want to THANK EVERYONE!!

The correct formula should be
{=IF(COUNTIFS($A$2:$A$10,$A13,$B$2:$B$10,"="),"",MAX(IF(
$A$2:$A$10=A13,$A13,$B$2:$B$10)))

???