How to find the latest date based on criteria

Copper Contributor



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.






2 Replies



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 


Here is a PivotTable solution.