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]

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

2

40

93

50

100

98

14

70

19

50

*

My Error Formula

50

70

93

98

100

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

3

*

*

*

*

*

*

*

*

*

*

Desires Results For Data Between 40,50 and 90,100

40

50

50

93

98

100

*

*

*

*

*

*

*

*

*

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