Multiple conditions in an array

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
Thank you very much Aladin ,

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

Many thanks

David
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top