Multiple conditions in an array

HI everybody ,

I am having an extremely hard time with the following problem .
I would like to find a formula that will allow the end user to count or sum
datas from a whole list depending on three different conditions with ,and
this is the important bit , the possibility to select "all criteria" of
condition 1 with one unique criteria of condition 2 , or with "all criteria"
for all conditions and so on ...

Here is the template :
Condition 1 Condition 2 Condition 3


test1 test a test red
test2 test b test blue
test3 test c test yellow
test4 test d test green
all tests all test all test


The user through a validated list can select any of the conditions ie ;
test1 : test a : red or all tests : all tests : all tests .


The datas :

Col A Col B Col C Col D
test1 test a test yellow Amount
test3 test a test blue Amount
test3 test c test yellow Amount
test4 test a test green Amount



I have tried the following formula but it doesn'work

{=SI(ET(condition1="all tests";condition 2="all tests";condition 3="all
tests");NBVAL(k2:k6);SI(et(condition 1=" all tests ";condition 2 ="all tests
";SOMME((k2:k6<>"")*(j2:j6=condition3));SI(et(condition 1=" all
tests";condition 3="all
tests";SOMME((k2:k6<>"")*(i2:i6=condition2));SI(et(condition2="all
tests";condition 3 =" all tests ";SOMME((k2:k6<>"")*(h2:h6=condition
1));SI(condition1="all
test";SOMME((k2:k6<>"")*(i2:i6=condition2)*(j2:j6=condition3));SI(condition2
="all
tests";SOMME((k2:k6<>"")*(h2:h6=condition1)*(j2:j6=condition3));SI(condition
3="all tests ";SOMME((k2:k6<>"")*(h2:h6=condition
1)*(i2:i6=condition2));SOMME((k2:k6<>"")*(h2:h6=condition1)*(i2:i6=condition
2)*(j2:j6=condition3))))))}


Any ideas ?

many thanks ;

david van DEa

[Aladin Akyurek]

Hi David,

Upon close reading I see it's a different problem than I thought.

I assume that you have 3 cells somewhere where the user selects desired conditions one by one from a dropdown list created by using data validation. And you named the first cell 'Condition1', the 2nd cell 'Condition2', and the 3rd cell 'Condition3'.

The possible values for Condition1 is

{"test1","test2","test3","test4","all tests"}

for Condition2

{"test a","test b","test c","test d","all tests"}

and finally for Condition3

{"test red","test blue","test yellow","test green","all tests"}

The relevant data wrt Condition1 is in H2:H6, wrt Condition2 in I2:I6, and wrt Condition3 in J2:J6.

The range that must be condtionally summed is K2:K6.

The formula that should compute the desired sums is:

=SUMPRODUCT((H2:H6=IF(Condition1="all tests",H2:H6,Condition1))*(I2:I6=IF(Condition2="all tests",I2:I6,Condition2))*(J2:J6=IF(Condition3="all tests",J2:J6,Condition3)),(K2:K6))

The formula that should compute the desired counts is:

=SUMPRODUCT((H2:H6=IF(Condition1="all tests",H2:H6,Condition1))*(I2:I6=IF(Condition2="all tests",I2:I6,Condition2))*(J2:J6=IF(Condition3="all tests",J2:J6,Condition3)))

Note. I forgat the French rendition of SUMPRODUCT, but, I thrust, you can find by looking at variations on SOMME.

Aladin

On 2002-03-14 02:21, Anonymous wrote:
HI everybody ,

I am having an extremely hard time with the following problem .
I would like to find a formula that will allow the end user to count or sum
datas from a whole list depending on three different conditions with ,and
this is the important bit , the possibility to select "all criteria" of
condition 1 with one unique criteria of condition 2 , or with "all criteria"
for all conditions and so on ...

Here is the template :
Condition 1 Condition 2 Condition 3


test1 test a test red
test2 test b test blue
test3 test c test yellow
test4 test d test green
all tests all test all test


The user through a validated list can select any of the conditions ie ;
test1 : test a : red or all tests : all tests : all tests .


The datas :

Col A Col B Col C Col D
test1 test a test yellow Amount
test3 test a test blue Amount
test3 test c test yellow Amount
test4 test a test green Amount



I have tried the following formula but it doesn'work

{=SI(ET(condition1="all tests";condition 2="all tests";condition 3="all
tests");NBVAL(k2:k6);SI(et(condition 1=" all tests ";condition 2 ="all tests
";SOMME((k2:k6<>"")*(j2:j6=condition3));SI(et(condition 1=" all
tests";condition 3="all
tests";SOMME((k2:k6<>"")*(i2:i6=condition2));SI(et(condition2="all
tests";condition 3 =" all tests ";SOMME((k2:k6<>"")*(h2:h6=condition
1));SI(condition1="all
test";SOMME((k2:k6<>"")*(i2:i6=condition2)*(j2:j6=condition3));SI(condition2
="all
tests";SOMME((k2:k6<>"")*(h2:h6=condition1)*(j2:j6=condition3));SI(condition
3="all tests ";SOMME((k2:k6<>"")*(h2:h6=condition
1)*(i2:i6=condition2));SOMME((k2:k6<>"")*(h2:h6=condition1)*(i2:i6=condition
2)*(j2:j6=condition3))))))}


Any ideas ?

many thanks ;

david van DEa

Thank you very much Aladin ,

The idea is there , do you want me to send you my file ?

Many thanks

David

[Aladin Akyurek]

On 2002-03-14 05:38, Anonymous wrote:
Thank you very much Aladin ,

The idea is there , do you want me to send you my file ?

Many thanks

David

Use both

aky@sem.hhs.nl
akyurek@xs4all.nl

Salut.

Aladin

It's me again Aladin ,

In order to make it work with all drop down list selected with ALL test , I need to add this to your formula : SI(ET($H$2=$AY$36;$J$2=$AY$36;$L$2=$AY$36);NBVAL($G$24:$G$1000); don't I ?

The formula you suggested me looks ok but doesn't count de <>"" cells in K2:k6 it only counts the occurences in H2:J6 .

It's good working with you ,

David

[Aladin Akyurek]

On 2002-03-14 05:56, Anonymous wrote:
It's me again Aladin ,

In order to make it work with all drop down list selected with ALL test , I need to add this to your formula : SI(ET($H$2=$AY$36;$J$2=$AY$36;$L$2=$AY$36);NBVAL($G$24:$G$1000); don't I ?

The formula you suggested me looks ok but doesn't count de <>"" cells in K2:k6 it only counts the occurences in H2:J6 .

It's good working with you ,

David

David,

Not sure I understand the issue.

Do you mean (a) summing works OK, but (b) counting not? If so, it looks like you want to include K2:K6 in the counts, then I'd suggest to expand the formula for counting with (LEN(K2:K6)>0), which becomes:

=SUMPRODUCT((H2:H6=IF(Condition1="all tests",H2:H6,Condition1))*(I2:I6=IF(Condition2="all tests",I2:I6,Condition2))*(J2:J6=IF(Condition3="all tests",J2:J6,Condition3))*(LEN(K2:K6)>0))

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-03-14 10:44 ]