Extract Data From Table vertically according to tow criterias

[Excel777]

hello,

i want to get data from first table vertically depending on two criterias value between 90,100
wish find non array formula
results coulmn M
taking in consinderation if i have two values on the same date the criteria applied in it it extract them.
thanks in advance for help
Sheet1

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9	*	Date	Extracted Values
2	1/1/2016	15	31	40	28	71	58	68	77	48	*	1/1/2016	*
3	1/2/2016	16	6	71	84	7	64	66	28	24	*	1/2/2016	*
4	1/3/2016	67	47	44	4	52	58	14	44	72	*	1/3/2016	*
5	1/4/2016	59	95	21	0	31	9	20	81	0	*	1/4/2016	95
6	1/5/2016	90	50	25	63	82	8	20	16	89	*	1/5/2016	90
7	1/6/2016	70	4	60	51	46	800	400	6	29	*	1/6/2016	*
8	1/7/2016	14	78	21	18	12	48	59	17	33	*	1/7/2016	*
9	1/8/2016	59	51	82	98	0	20	120	17	0	*	1/8/2016	98
10	1/9/2016	70	23	46	2	54	85	36	53	58	*	1/9/2016	*
11	1/10/2016	1	9	18	68	35	63	82	68	899	*	1/10/2016	*
12	1/11/2016	30	32	95	76	2	7	59	57	38	*	1/11/2016	95
13	1/12/2016	15	33	7	94	28	89	1	19	21	*	1/12/2016	94
14	1/13/2016	68	4	3	36	9	91	46	45	47	*	1/13/2016	91
15	1/14/2016	15	36	41	71	23	15	94	9	64	*	1/14/2016	94
16	1/15/2016	79	89	27	4	25	25	40	95	32	*	1/15/2016	95
17	1/16/2016	61	21	62	58	17	32	26	69	7	*	1/16/2016	*
18	1/17/2016	55	77	82	20	21	55	66	33	71	*	1/17/2016	*
19	1/18/2016	3	71	68	35	6	62	76	3	27	*	1/18/2016	*
20	1/19/2016	3	43	3	98	75	69	48	78	3	*	1/19/2016	98
21	1/20/2016	3	61	28	35	83	72	96	46	16	*	1/20/2016	96
22	1/21/2016	4	39	28	25	24	91	3	30	49	*	1/21/2016	91

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Excel777]

Hello,

 

[Peter_SSs]

Sorry for that,

I need the final results in that order 40,50,50,93,100,98 by using aggregate function.... this is the final request

You didn't answer the question.

40 , 50 , 50 , 93 , 100, 98

What is your actual criteria?
- Is it 40-50 and 90-100 like the above example appears, and as your earlier posts requested?
- Or is it as below?

Actually it's 100 apart
First criteria between 100,110
Second one between 200,210
And so on till 1000,1010

 

[Excel777]

Actual criteria is between 40,50 and between 90,100 and taking in consideration results in this order 40,50,50,93,100,98 and using aggregate function

 

[Excel777]

Is it clear now

 

[Peter_SSs]

Is it clear now

Yes it is, but it was after midnight my time when you posted this, so be patient as we are not all in your time zone.

=IFERROR(INDEX($B2:$J2,IF(COLUMNS($M2:M2)<=COUNTIFS($B2:$J2,">=40",$B2:$J2,"<=50"),
     AGGREGATE(15,6,(COLUMN($B2:$J2)-COLUMN($B2)+1)/(ABS($B2:$J2-45)<=5),COLUMNS($M2:M2)),
     AGGREGATE(15,6,(COLUMN($B2:$J2)-COLUMN($B2)+1)/(ABS($B2:$J2-95)<=5),COLUMNS($M2:M2)-COUNTIFS($B2:$J2,">=40",$B2:$J2,"<=50")))),"")

 

[Excel777]

many thanks dear i know i tired you, this solution helps and works for me and do exactly what i need, wish i can modify in it easily and still works by adding more criterias i'll not ask any other question now

 

[Peter_SSs]

.. this solution helps and works for me and do exactly what i need,..

Good news.

 

[Excel777]

I failed to add third criteria to the formula which is between 60,70 I am so sad