LARGE Function using a date range

[SarahR2908]

Im trying to show the Top 5 performers within a date range using the units per hour (column G)
At the moment im using the following formula (for first place) which gives the answer i need but you have to manually put in the date range.
=LARGE('Sheet 1'!$G$2:$G$60,1)
I want the user to be able to put in the to and from dates in cells B1 and D1 on another tab and the formula uses these dates to then calculate the largest within that period.

Ive tried to use a formula adapted from a MAX function but this doesnt work.
=LARGE(IF(('Sheet 1'!$B$2:$B$15000<=D1)*('Sheet 1'!$B$2:$B$15000>=B1),'Sheet 1'!$G$2:$G$15000,""))

Can anyone help?

 

[AlanY]

yes, you're right. you need an array formula for this

Book1
	A	B	C	D	E	F
1	Date	per Hrs			11/01/2020	12/01/2020
2	10/01/2020	339		1	65
3	10/01/2020	278		2	67
4	10/01/2020	262		3	72
5	10/01/2020	121		4	100
6	10/01/2020	145		5	104
7	10/01/2020	217
8	10/01/2020	132
9	10/01/2020	345
10	10/01/2020	71
11	10/01/2020	132
12	11/01/2020	180
13	11/01/2020	219
14	11/01/2020	65
15	11/01/2020	144
16	11/01/2020	298
17	11/01/2020	72
18	11/01/2020	273
19	11/01/2020	151
20	11/01/2020	67
21	11/01/2020	182
22	12/01/2020	208
23	12/01/2020	298
24	12/01/2020	177
25	12/01/2020	250
26	12/01/2020	309
27	12/01/2020	156
28	12/01/2020	104
29	12/01/2020	191
30	12/01/2020	100
31	12/01/2020	213
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	{=SMALL(IF($A$2:$A$31>=$E$1,IF(($A$2:$A$31<=$F$1),$B$2:$B$31)),$D2)}
B2:B31	B2	=RANDBETWEEN(50,350)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[SarahR2908]

You are a star! Thanks so much for your help on this

 

[AlanY]

or to modify the LARGE() formula like this

if you have 15000+ rows, the sumproduct() (non array) option should be much faster

Book1
	A	B	C	D	E	F
1	Date	per Hrs			11/01/2020	12/01/2020
2	10/01/2020	153		1	136
3	10/01/2020	125		2	140
4	10/01/2020	284		3	146
5	10/01/2020	179		4	155
6	10/01/2020	131		5	171
7	10/01/2020	329
8	10/01/2020	148
9	10/01/2020	137
10	10/01/2020	280
11	10/01/2020	94
12	11/01/2020	219
13	11/01/2020	140
14	11/01/2020	146
15	11/01/2020	239
16	11/01/2020	155
17	11/01/2020	350
18	11/01/2020	104
19	11/01/2020	281
20	11/01/2020	249
21	11/01/2020	177
22	12/01/2020	313
23	12/01/2020	339
24	12/01/2020	224
25	12/01/2020	319
26	12/01/2020	136
27	12/01/2020	233
28	12/01/2020	265
29	12/01/2020	171
30	12/01/2020	305
31	12/01/2020	232
Sheet2

Cell Formulas
Range	Formula
E2:E6	E2	=SUMPRODUCT(LARGE(($A$2:$A$31>=$E$1)*($A$2:$A$31<=$F$1)*($B$2:$B$31),COUNTIFS($A$2:$A$31,">="&$E$1,$A$2:$A$31,"<="&$F$1)-$D2))
B2:B31	B2	=RANDBETWEEN(50,350)