hello ..... i have 2 questions :

first question :
given :

A B
1 max 20
2 cliff 5
3 cliff 38
4 max 40
5 cliff 60
6 dot 50
7 cliff 60
8 cliff 60
9 cliff 78
10 cliff 60
11 dot 44

i have entered in cell C1 : "max"
NOW i want a formula in cell D1 which lets me have ALL
related results to "max"

second question :
given :

A B C D
1 a 10 2 d 40
2 b 20 3 e 50
3 b 30 4 f 60

i want the search string cell to be E1 in order to enter : a ,b,c,d,e,f
in order to get their correspondent results
WHAT TO USE HERE ?? index(...(match)).....maybe ?

HI Pierre,

1 . In cell D1 enter the following formula

=SUMIF($B$1:$B$11,D1,$C$1:$C$11)


this will get your answer. If you then enter the other names in the other
cells you can fill down.

2 . Set your table up like this;

Field1 Field2 Field1 Field2 Field1
a 10 d 40 =a
b 20 e 50 =b
c 30 f 60 =c
=d
=e
=f

the = sign is important i.e. enter '=a

In cell f1 enter =DSUM(A1:B4,"field2",E1:E7)+DSUM(C1:D4,"field2",E1 :E7)

Viola

You can delete out criteria from the range f1 to f6 as neccessary.

Cheers

Flower

"pierre" wrote:

hello ..... i have 2 questions :

first question :
given :

A B
1 max 20
2 cliff 5
3 cliff 38
4 max 40
5 cliff 60
6 dot 50
7 cliff 60
8 cliff 60
9 cliff 78
10 cliff 60
11 dot 44

i have entered in cell C1 : "max"
NOW i want a formula in cell D1 which lets me have ALL
related results to "max"

second question :
given :

A B C D
1 a 10 2 d 40
2 b 20 3 e 50
3 b 30 4 f 60

i want the search string cell to be E1 in order to enter : a ,b,c,d,e,f
in order to get their correspondent results
WHAT TO USE HERE ?? index(...(match)).....maybe ?

it didnt worked....
as for first question :
i want to write the formula in D1 and to drag it down while to be sure that
this formula will show me all results related to "max" separately .i.e :
5 ,38,60,60,78,60

as for second question ....it didnt work



"Flower" wrote:

HI Pierre,

1 . In cell D1 enter the following formula

=SUMIF($B$1:$B$11,D1,$C$1:$C$11)


this will get your answer. If you then enter the other names in the other
cells you can fill down.

2 . Set your table up like this;

Field1 Field2 Field1 Field2 Field1
a 10 d 40 =a
b 20 e 50 =b
c 30 f 60 =c
=d
=e
=f

the = sign is important i.e. enter '=a

In cell f1 enter =DSUM(A1:B4,"field2",E1:E7)+DSUM(C1:D4,"field2",E1 :E7)

Viola

You can delete out criteria from the range f1 to f6 as neccessary.

Cheers

Flower

"pierre" wrote:

hello ..... i have 2 questions :

first question :
given :

A B
1 max 20
2 cliff 5
3 cliff 38
4 max 40
5 cliff 60
6 dot 50
7 cliff 60
8 cliff 60
9 cliff 78
10 cliff 60
11 dot 44

i have entered in cell C1 : "max"
NOW i want a formula in cell D1 which lets me have ALL
related results to "max"

second question :
given :

A B C D
1 a 10 2 d 40
2 b 20 3 e 50
3 b 30 4 f 60

i want the search string cell to be E1 in order to enter : a ,b,c,d,e,f
in order to get their correspondent results
WHAT TO USE HERE ?? index(...(match)).....maybe ?

SORRY....
i want to write the formula in D1 and to drag it down while to be sure that
this formula will show me all results related to "cliff" separately .i.e :
5 ,38,60,60,78,60




"pierre" wrote:

it didnt worked....
as for first question :
i want to write the formula in D1 and to drag it down while to be sure that
this formula will show me all results related to "max" separately .i.e :
5 ,38,60,60,78,60

as for second question ....it didnt work



"Flower" wrote:

HI Pierre,

1 . In cell D1 enter the following formula

=SUMIF($B$1:$B$11,D1,$C$1:$C$11)


this will get your answer. If you then enter the other names in the other
cells you can fill down.

2 . Set your table up like this;

Field1 Field2 Field1 Field2 Field1
a 10 d 40 =a
b 20 e 50 =b
c 30 f 60 =c
=d
=e
=f

the = sign is important i.e. enter '=a

In cell f1 enter =DSUM(A1:B4,"field2",E1:E7)+DSUM(C1:D4,"field2",E1 :E7)

Viola

You can delete out criteria from the range f1 to f6 as neccessary.

Cheers

Flower

"pierre" wrote:

hello ..... i have 2 questions :

first question :
given :

A B
1 max 20
2 cliff 5
3 cliff 38
4 max 40
5 cliff 60
6 dot 50
7 cliff 60
8 cliff 60
9 cliff 78
10 cliff 60
11 dot 44

i have entered in cell C1 : "max"
NOW i want a formula in cell D1 which lets me have ALL
related results to "max"

second question :
given :

A B C D
1 a 10 2 d 40
2 b 20 3 e 50
3 b 30 4 f 60

i want the search string cell to be E1 in order to enter : a ,b,c,d,e,f
in order to get their correspondent results
WHAT TO USE HERE ?? index(...(match)).....maybe ?

Regarding 1,

Why not use a filter on the table?

Regarding 2, what error message are you getting?



"pierre" wrote:

SORRY....
i want to write the formula in D1 and to drag it down while to be sure that
this formula will show me all results related to "cliff" separately .i.e :
5 ,38,60,60,78,60




"pierre" wrote:

it didnt worked....
as for first question :
i want to write the formula in D1 and to drag it down while to be sure that
this formula will show me all results related to "max" separately .i.e :
5 ,38,60,60,78,60

as for second question ....it didnt work



"Flower" wrote:

HI Pierre,

1 . In cell D1 enter the following formula

=SUMIF($B$1:$B$11,D1,$C$1:$C$11)


this will get your answer. If you then enter the other names in the other
cells you can fill down.

2 . Set your table up like this;

Field1 Field2 Field1 Field2 Field1
a 10 d 40 =a
b 20 e 50 =b
c 30 f 60 =c
=d
=e
=f

the = sign is important i.e. enter '=a

In cell f1 enter =DSUM(A1:B4,"field2",E1:E7)+DSUM(C1:D4,"field2",E1 :E7)

Viola

You can delete out criteria from the range f1 to f6 as neccessary.

Cheers

Flower

"pierre" wrote:

hello ..... i have 2 questions :

first question :
given :

A B
1 max 20
2 cliff 5
3 cliff 38
4 max 40
5 cliff 60
6 dot 50
7 cliff 60
8 cliff 60
9 cliff 78
10 cliff 60
11 dot 44

i have entered in cell C1 : "max"
NOW i want a formula in cell D1 which lets me have ALL
related results to "max"

second question :
given :

A B C D
1 a 10 2 d 40
2 b 20 3 e 50
3 b 30 4 f 60

i want the search string cell to be E1 in order to enter : a ,b,c,d,e,f
in order to get their correspondent results
WHAT TO USE HERE ?? index(...(match)).....maybe ?