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]

something like this?

Book1
	A	B	C	D	E	F
1	Date	per Hrs			11/01/2020	12/01/2020
2	10/01/2020	151		1	315
3	10/01/2020	192		2	312
4	10/01/2020	198		3	309
5	10/01/2020	201		4	297
6	10/01/2020	193		5	275
7	10/01/2020	58
8	10/01/2020	337
9	10/01/2020	65
10	10/01/2020	54
11	10/01/2020	87
12	11/01/2020	275
13	11/01/2020	235
14	11/01/2020	312
15	11/01/2020	154
16	11/01/2020	213
17	11/01/2020	52
18	11/01/2020	182
19	11/01/2020	264
20	11/01/2020	184
21	11/01/2020	221
22	12/01/2020	315
23	12/01/2020	214
24	12/01/2020	252
25	12/01/2020	309
26	12/01/2020	173
27	12/01/2020	92
28	12/01/2020	297
29	12/01/2020	149
30	12/01/2020	58
31	12/01/2020	228
Sheet6

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),$D2))
B2:B31	B2	=RANDBETWEEN(50,350)

 

[KOKOSEK]

Instead of D2 in this formula above use ROW()-1 and you do not need numbers in col. D.

 

[SarahR2908]

Thanks for your help. I just seem to be getting 0
Formula: =SUMPRODUCT(LARGE(('Sheet1'!$B$2:$B$15000>=$B$1)*('Sheet1'!$B$2:$B$15000<=$D$1)*('Sheet1'!$G$2:$G$15000),A5))

Have i missed something?
On sheet 1 column B is the dates, Column G is the no of units

 

[AlanY]

it looks ok, try to cut down the ranges to see what you get

=SUMPRODUCT(LARGE(('Sheet1'!$B$2:$B$15>=$B$1)*('Sheet1'!$B$2:$B$15<=$D$1)*('Sheet1'!$G$2:$G$15),A5))

 

[SarahR2908]

OMG just realised i put 2019 in the criteria but this is for data from 2020.
No wonder i was getting 0

Its all working now!
Thank you

 

[AlanY]

i missed that as well,
glad that work out for you

 

[SarahR2908]

Can i use this formula amending LARGE for SMALL so i can get the bottom 5 performers?
Assuming i need to amend something else as just changing it gives me 0
=SUMPRODUCT(SMALL(('UNIT SCAN'!$B$2:$B$15>=$B$1)*('UNIT SCAN'!$B$2:$B$15<=$D$1)*('UNIT SCAN'!$G$2:$G$15),F5))

 

[AlanY]

the SMALL() function should work as long as you don't have any empty cells within the Col G range

 

[SarahR2908]

I've checked and no empty cells but still only bringing back 0
do i still use 1, 2, 3 as in the order?