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.

 

[svendiamond]

Ok ok... I see what happened. In cell B5 on Sheet2, enter the following formula and CTRL+SHIFT+ENTER:

=IF(COUNTIFS($B$11:$B$58,"dd-mmm-yyyy",$A$11:$A$58,A5)>0,"pending",MAX(IF($A$11:$A$58=A5,$B$11:$B$58)))

 

[fairchance]

Ok Sir, It is working So nice of you. Take good care of your health and be happy. Thank you

 

[Cosmic Wizard]

Is it not just this?

=COUNTIFS(F2:F11,MIN(F2:F11))

My mistake, I was trying to count up the formulas =ABS(C9-D9), which does not work.

Only when I convert the formulas to number does it work: thankyou!!

 

[fairchance]

Dear All

This formula is without Ctrl+Shift+Enter

=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))

Cheers up