Validation for date required with lowest number

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
387
Hi Team,

I have below data, looking for a formula in column F to see which is the lowest number in column E for column A. Expected result provided in column F.

Column AColumn BColumn CColumn DColumn EColumn F
Numbernamedatetodays datetoday date - dateResult
17137BD04/07/201930/07/201926TRUE
17137BR17/05/201930/07/201974
17137EP04/07/201930/07/201926TRUE
17137LD04/07/201930/07/201926TRUE
17137PA14/06/201930/07/201946
17137TS20/05/201930/07/201971
18370BB20/05/201930/07/201971
18370BR20/05/201930/07/201971
18370EP20/05/201930/07/201971
18370LD20/05/201930/07/201971
18370PA19/06/201930/07/201941
18370TS29/05/201930/07/201962TRUE

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Re: Validation for date required with lowest number - Urgent

try

=IF(E3=MINIFS($E$3:$E$14,$A$3:$A$14,A3),TRUE,"")
 
Upvote 0
Re: Validation for date required with lowest number - Urgent

MAXIFS and MINIFS is only available in latest versions of Excel
Solution using MAXIFS in column F
Alternative using AGGREGATE in column H

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
1
Numbernamedatetodays datetoday date - dateResult
(MAXIFS)
formula in F2 copied downResult
(AGGREGATE)
formula in F2 copied down
2
17137​
BD
04/07/2019​
30/07/2019​
26​
TRUE
=MINIFS(E:E,A:A,A2)=E2
TRUE
=AGGREGATE(15,6,E:E/(A:A=A2),1)=E2
3
17137​
BR
17/05/2019​
30/07/2019​
74​
FALSE​
FALSE​
4
17137​
EP
04/07/2019​
30/07/2019​
26​
TRUE
TRUE
5
17137​
LD
04/07/2019​
30/07/2019​
26​
TRUE
TRUE
6
17137​
PA
14/06/2019​
30/07/2019​
46​
FALSE​
FALSE​
7
17137​
TS
20/05/2019​
30/07/2019​
71​
FALSE​
FALSE​
8
18370​
BB
20/05/2019​
30/07/2019​
71​
FALSE​
FALSE​
9
18370​
BR
20/05/2019​
30/07/2019​
71​
FALSE​
FALSE​
10
18370​
EP
20/05/2019​
30/07/2019​
71​
FALSE​
FALSE​
11
18370​
LD
20/05/2019​
30/07/2019​
71​
FALSE​
FALSE​
12
18370​
PA
19/06/2019​
30/07/2019​
41​
TRUE
TRUE
13
18370​
TS
29/05/2019​
30/07/2019​
62​
FALSE​
FALSE​
14
Sheet: Sheet4
 
Upvote 0
Re: Validation for date required with lowest number - Urgent

.. and another one that should work in all versions.

Excel Workbook
ABCDEF
1Numbernamedatetodays datetoday date - dateResult
217137BD4/07/201930/07/201926TRUE
317137BR17/05/201930/07/201974
417137EP4/07/201930/07/201926TRUE
517137LD4/07/201930/07/201926TRUE
617137PA14/06/201930/07/201946
717137TS20/05/201930/07/201971
818370BB20/05/201930/07/201971
918370BR20/05/201930/07/201971
1018370EP20/05/201930/07/201971
1118370LD20/05/201930/07/201971
1218370PA19/06/201930/07/201941TRUE
1318370TS29/05/201930/07/201962
Minimums
 
Last edited:
Upvote 0
Re: Validation for date required with lowest number - Urgent

Hi Peter,

Formula isnt working it says - The formula is missing an opening or closing parentthesis
 
Upvote 0
Re: Validation for date required with lowest number - Urgent

Hi Peter,

Formula isnt working it says - The formula is missing an opening or closing parentthesis
My mistake - the forum often cuts off formulas that contain an < sign. That F2 formula should have been

=IF(SUMPRODUCT(--(A$2:A$13=A2),--(E$2:E$13<E2)),"",TRUE)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top