Formula to calculate largest value, only when applicable

[fairchance]

Dear All

Hi

We would like to come up with latest date for each site , but we only need the largest date to show up in the summary section only when all dates are completed for each site, otherwise it should show "Pending")

See thel link below for further details

https://www.dropbox.com/s/72le2f7kdugv90a/Dates.xlsx?dl=0

Kindly help me out

Regards

Shehbaz H.

 

[NBVC]

Try:

=IF(COUNTIFS($A$11:$A$58,A3,$B$11:$B$58,"dd-mmm-yyyy")>0,"pending",AGGREGATE(14,6,$B$11:$B$58/($A$11:$A$58=A3),1))

copied down

 

[fairchance]

Try:

=IF(COUNTIFS($A$11:$A$58,A3,$B$11:$B$58,"dd-mmm-yyyy")>0,"pending",AGGREGATE(14,6,$B$11:$B$58/($A$11:$A$58=A3),1))

copied down

Thank you Sir, it is working