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]

I didn't like how your tables were lined up, sooo assuming your raw data is in columns A, B, and C on Sheet1, and your lookup table is on Sheet2 columns A, B, and C... then on Sheet2 in cell B2, you could enter the following array formula:

=IF(COUNTIFS(Sheet1!B:B,"dd-mmm-yyyy",Sheet1!A:A,A2)>0,"pending",MAX(IF(Sheet1!A:A=A2,Sheet1!B:B)))

CTRL+SHIFT+ENTER

and it will return either "pending" or the latest date that matches the site number.

 

[fairchance]

Dear Sir,

Thank you for reply. I have put your given formula "Pending" criteria is working while actual max dates are showing 0 instead of latest date. Could you sir, please return my file after implementing your proposed formula?

Regards

Shehbaz H.

 

[svendiamond]

Did you press CTRL+SHIFT+ENTER after you entered the formula? Not just ENTER... it's an array formula.

 

[Cosmic Wizard]

Did you press CTRL+SHIFT+ENTER after you entered the formula? Not just ENTER... it's an array formula.

Actually this is similar to a problem i can't seem to solve.

I need to count up the minimal number of minutes in a column.

ie if the column had say 12 values, in minute format, like 0:10, 0:40, 0:20, 0:30, 0:10.

So I need to count up the lowest value, in this case 0:10, which would be 2.

I have tried all sort of formulas to no luck: =SUM((MINUTE(F2:F11)=10)*1) does work but I need to use two formulas.

Any help would be greatly apprieciated!!

 

[Aladin Akyurek]

Actually this is similar to a problem i can't seem to solve.

I need to count up the minimal number of minutes in a column.

ie if the column had say 12 values, in minute format, like 0:10, 0:40, 0:20, 0:30, 0:10.

So I need to count up the lowest value, in this case 0:10, which would be 2.

I have tried all sort of formulas to no luck: =SUM((MINUTE(F2:F11)=10)*1) does work but I need to use two formulas.

Any help would be greatly apprieciated!!

Is it not just this?

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

 

[fairchance]

Sir, I have tried but failed. Can you provide another option?

Kind Regards

Shehbaz H.

 

[fairchance]

Sir, here is the file with wrong result you can see the formula is in array form.

https://www.dropbox.com/s/90xlydjotzk3xvx/Dates1.xlsx?dl=0

Please help

kind regards

Shehbaz H.

 

[svendiamond]

Ok Shehbaz, in your attached sheet, in cell B2, enter the following formula, and press CTRL+SHIFT+ENTER:

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


And also, that problem about the minutes in a certain column should be made in a new post on the forum... that is a completely different issue...

 

[fairchance]

Sir, same issue please check sheet2

https://www.dropbox.com/s/90xlydjotzk3xvx/Dates1.xlsx?dl=0

Regards

Shehbaz H.