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

Group1

Group2

Group3

Group4

Group5

Group6

Group7

Group8

Group9

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Date[/TD]

[TD="align: center"]Date[/TD]
[TD="align: center"]Extracted Values[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1/1/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]48[/TD]

[TD="align: center"]1/1/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]1/2/2016[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]84[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]64[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]24[/TD]

[TD="align: center"]1/2/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]1/3/2016[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]72[/TD]

[TD="align: center"]1/3/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]1/4/2016[/TD]
[TD="align: center"]59[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]1/4/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]1/5/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]90[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]89[/TD]

[TD="align: center"]1/5/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]90[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]1/6/2016[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]29[/TD]

[TD="align: center"]1/6/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]1/7/2016[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]33[/TD]

[TD="align: center"]1/7/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]1/8/2016[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]82[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]1/8/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]1/9/2016[/TD]
[TD="align: center"]70[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]58[/TD]

[TD="align: center"]1/9/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1/10/2016[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]899[/TD]

[TD="align: center"]1/10/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]1/11/2016[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]32[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]38[/TD]

[TD="align: center"]1/11/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]1/12/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]7[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]21[/TD]

[TD="align: center"]1/12/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]1/13/2016[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]9[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]

[TD="align: center"]1/13/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]1/14/2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]41[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]15[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]64[/TD]

[TD="align: center"]1/14/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]94[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: center"]1/15/2016[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]40[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]
[TD="align: center"]32[/TD]

[TD="align: center"]1/15/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]95[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]1/16/2016[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]1/16/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]1/17/2016[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]82[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]71[/TD]

[TD="align: center"]1/17/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]1/18/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"]68[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]27[/TD]

[TD="align: center"]1/18/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]1/19/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]1/19/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]98[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]1/20/2016[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]72[/TD]
[TD="bgcolor: #a9d08e, align: center"]96[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]16[/TD]

[TD="align: center"]1/20/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]96[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: center"]1/21/2016[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]24[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]49[/TD]

[TD="align: center"]1/21/2016[/TD]
[TD="bgcolor: #a9d08e, align: center"]91[/TD]

</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