# Formula to calculate largest value, only when applicable

#### fairchance

##### Board Regular
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### svendiamond

##### Well-known Member
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

##### Board Regular
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

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

#### Cosmic Wizard

##### Board Regular

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!!

##### MrExcel MVP
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

##### Board Regular

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

Kind Regards

Shehbaz H.

#### svendiamond

##### Well-known Member
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...

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,025
Messages
5,835,016
Members
430,332
Latest member
Charly_Moon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back