How to find the latest date based on criteria

Copper Contributor

Hi,

 

Can someone please help me in developing a formula/pivot that allows me to find the latest date based on some criteria. Below is the example Excel that is similar to what I am using:

 

Fund NameValuation DateData Received
Fund 131/12/21Yes
Fund 131/03/22Yes
Fund 130/06/22Yes
Fund 130/09/22Yes
Fund 131/12/22No
Fund 231/12/21Yes
Fund 231/03/22Yes
Fund 230/06/22Yes
Fund 230/09/22No
Fund 231/12/22No

 

In the output I would expect the fund name and the latest date (where data received = yes) to be returned as per the below:

 

Fund NameValuation Date
Fund 130/09/22
Fund 230/06/22

 

Any help would be greatly appreciated.

 

Thanks,

 

Danny

 

2 Replies

@DannyL95 

=LARGE(IF(($A$2:$A$11=E2)*($C$2:$C$11="Yes"),$B$2:$B$11),1)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.

latest date based on criteria.JPG 

@DannyL95 

Here is a PivotTable solution.

Patrick2788_0-1673718409721.png