G
Guest
Guest
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
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