Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Multiple conditions in an array

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    Thank you very much Aladin ,

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

    Many thanks

    David

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •