Top 10 Value in a date range with multiple criteria

[conorrey]

Hi,

I have been trying to work out a formula to find a specific value in a large range of data.

I need to find our top 10 RES between a date range between another date range and return the RES number.

I will then use index match to get the whole row of date to create a top 10 list.

Below is the sample data with criteria.

I have tried using Max, Large and Aggregate formulas but I cannot add the date range. It just looks at the whole range

Any help would be great.

Arrival	Res #	TOTAL	Status	Created
01/09/2023	223567	10000.00	Waitlist	02/01/2023
30/09/2023	223597	124.67	Departed	03/01/2023
03/01/2023	223598	232.60	Departed	03/04/2023
13/05/2023	223625	702.95	Departed	03/08/2023
05/01/2023	223660	423.35	Departed	03/11/2023
05/01/2023	223663	343.61	Departed	03/10/2023
29/03/2023	223712	1938.33	Cancelled	20/11/2023
29/03/2023	223713	5242.29	Cancelled	20/10/2023
15/09/2023	223770	435.78	Waitlist	21/01/2023
18/01/2023	223771	1304.37	Waitlist	21/01/2023
10/01/2023	223789	68.28	Departed	20/10/2023
08/03/2023	223796	1794.36	Departed	07/12/2023
11/01/2023	223797	0.00	Departed	06/11/2023
06/01/2023	223799	225.11	Departed	10/07/2023
18/09/2023	223802	1473.12	Cancelled	20/01/2023
		Created	01/01/2023​	30/04/2023​
		Arrival	01/09/2023​	30/09/2023​
		Status	Waitlist
		Total	Max		Top 10
		Result	Column D

 

[Anthony47]

And which Office version do you use?

 

[conorrey]

Hi
I use Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20182) 64-bit.

Conor

 

[Fluff]

I use Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20182) 64-bit.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[conorrey]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi, Thanks I have done that now.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E
1	Arrival	Res #	TOTAL	Status	Created
2	01/09/2023	223567	10000	Waitlist	02/01/2023
3	30/09/2023	223597	124.67	Departed	03/01/2023
4	03/01/2023	223598	232.6	Departed	03/04/2023
5	13/05/2023	223625	702.95	Departed	03/08/2023
6	05/01/2023	223660	423.35	Departed	03/11/2023
7	05/01/2023	223663	343.61	Departed	03/10/2023
8	29/03/2023	223712	1938.33	Cancelled	20/11/2023
9	29/03/2023	223713	5242.29	Cancelled	20/10/2023
10	15/09/2023	223770	435.78	Waitlist	21/01/2023
11	18/01/2023	223771	1304.37	Waitlist	21/01/2023
12	10/01/2023	223789	68.28	Departed	20/10/2023
13	08/03/2023	223796	1794.36	Departed	07/12/2023
14	11/01/2023	223797	0	Departed	06/11/2023
15	06/01/2023	223799	225.11	Departed	10/07/2023
16	18/09/2023	223802	1473.12	Cancelled	20/01/2023
17
18
19	Created	01/01/2023	30/04/2023
20	Arrival	01/09/2023	30/09/2023
21	Status	Waitlist
22	Total	Max	10
23	Result
24	01/09/2023	223567	10000	Waitlist	02/01/2023
25	15/09/2023	223770	435.78	Waitlist	21/01/2023
26
Sheet6

Cell Formulas
Range	Formula
A24:E25	A24	=TAKE(SORT(FILTER(A2:E16,(A2:A16>=B20)*(A2:A16<=C20)*(E2:E16>=B19)*(E2:E16<=C19)*(D2:D16=B21)),3,-1),C22)
Dynamic array formulas.

 

[conorrey]

Hi,

I tried to use that formula but I am getting a #calc error.

Below is the formula i use

=TAKE(SORT(FILTER(A2:P10000,(A2:A10000>=S11)*(A2:A10000<=T11)*(P2:P10000>=S10)*(P2:P10000<=T10)*(O2:O10000=S12)),3,-1),T13)

any help would be great

Range	A2:P10000
Column P	Created	01/01/2023​	31/12/2023​
Column A	Arrival	01/01/2023​	31/12/2023​
Column O	Status	Waitlist
	Total	Max	10​
	Result
	#CALC!​	#CALC!​	#CALC!​

 

[Fluff]

The error suggests that you don't have any data that meets all the criteria.

 

[conorrey]

The error suggests that you don't have any data that meets all the criteria.

Thanks. i have adjusted the formula now to the correct range and it works perfect. Thanks for your help

 

[conorrey]

The error suggests that you don't have any data that meets all the criteria.

HI, the formula is picking up the max value in Column A ( a Date). how do I get it to pick the largest/max number in column L.

Below is my wokring formula but it is picking the largest date

=TAKE(SORT(FILTER('Current Week'!A:P,('Current Week'!A:A>=D86)*('Current Week'!A:A<=E86)*('Current Week'!P:P>=D85)*('Current Week'!P:P<=E85)*('Current Week'!O:O=D87)),3,-1),E88)

Any help would be great