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]

so the rsults can strat from column M and drag across according the number of values found (one found, tow founds, three founds.......

 

[Excel777]

any help?

 

[MARZIOTULLIO]

Hi

try

M2 =SUMPRODUCT(($A$2:$A$22=L2)*(B2:J2>=90)*(B2:J2<=100)*(B2:J2)) copy down

for hide zero format column M as [=0]""

 

[Excel777]

Hi

try

M2 =SUMPRODUCT(($A$2:$A$22=L2)*(B2:J2>=90)*(B2:J2<=100)*(B2:J2)) copy down


for hide zero format column M as [=0]""

its so great dear but i have have two values meet the condition at the same date how can i get them?

 

[Excel777]

any ideas to get the two value at same date meet the criteria metioned

 

[Excel777]

?

 

[Peter_SSs]

In M2, copied across and down, try

=IF(LARGE($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2))>=90,LARGE($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2)),"")

You didn't say that order was important so this extracts from largest down.

 

[Excel777]

First of all many thanks dear, for your help,

i tried to make a modification in the formula by adding two more criterias and to get results from smallest to largest but it failed can you help to fix the error
Sheet1

*

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffffcc, align: center"]40[/TD]
[TD="bgcolor: #ffffcc, align: center"]93[/TD]
[TD="bgcolor: #ffffcc, align: center"]50[/TD]
[TD="bgcolor: #ffffcc, align: center"]100[/TD]
[TD="bgcolor: #ffffcc, align: center"]98[/TD]
[TD="bgcolor: #ffffcc, align: center"]14[/TD]
[TD="bgcolor: #ffffcc, align: center"]70[/TD]
[TD="bgcolor: #ffffcc, align: center"]19[/TD]
[TD="bgcolor: #ffffcc, align: center"]50[/TD]

[TD="align: center"]My Error Formula[/TD]
[TD="bgcolor: #ffff00, align: center"]50[/TD]
[TD="bgcolor: #ffff00, align: center"]70[/TD]
[TD="bgcolor: #ffff00, align: center"]93[/TD]
[TD="bgcolor: #ffff00, align: center"]98[/TD]
[TD="bgcolor: #ffff00, align: center"]100[/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]
[TD="bgcolor: #ffff00"]#NUM![/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: center"]Desires Results For Data Between 40,50 and 90,100[/TD]
[TD="bgcolor: #99cc00, align: center"]40[/TD]
[TD="bgcolor: #99cc00, align: center"]50[/TD]
[TD="bgcolor: #99cc00, align: center"]50[/TD]
[TD="bgcolor: #99cc00, align: center"]93[/TD]
[TD="bgcolor: #99cc00, align: center"]98[/TD]
[TD="bgcolor: #99cc00, align: center"]100[/TD]
[TD="bgcolor: #99cc00, align: center"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]
[TD="bgcolor: #99cc00"]*[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula M2 =IF(SMALL($B2:$J2,COUNTIF($B2:$J2,">50")+COLUMNS($M2:M2))>=40,SMALL($B2:$J2,COUNTIF($B2:$J2,">50")+COLUMNS($M2:M2)), IF(SMALL($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2))>=90,SMALL($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2)),"")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

In M2, copied across and down, tr

=IF(LARGE($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2))>=90,LARGE($B2:$J2,COUNTIF($B2:$J2,">100")+COLUMNS($M2:M2)),"")

You didn't say that order was important so this extracts from largest down.

 

[Peter_SSs]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Group1	Group2	Group3	Group4	Group5	Group6	Group7	Group8	Group9		Date	Values
2	1/1/2016	40	93	50	100	98	14	70	19	50		1/1/2016	40	50	50	93	98	100
Check values (2)