Formula to calculate largest value, only when applicable

fairchance

Board Regular
Joined
Jan 4, 2015
Messages
110
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 4, 2015
Messages
110
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
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
Did you press CTRL+SHIFT+ENTER after you entered the formula? Not just ENTER... it's an array formula.
 

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jan 4, 2015
Messages
110

ADVERTISEMENT

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

Kind Regards

Shehbaz H.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,289
Members
414,223
Latest member
Accountant2B

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top