Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I have a SumProduct formula which works fine and includes a criterion <range>=M43 where M43 is simply a cell reference.

    A similar formula works correctly with <range>=M44.

    Now I want to sum the results of the two formulae. I could of course just add them together, but I want to keep things as simple as possible (in what is already rather a complex formula) despite applying extra criteria to the whole thing.

    So I thought I would substitute, as the criterion in question, OR(<range>=M43,<range>=M44) but - to my surprise - the SumProduct selects far too many results. I tested this by using just OR(<range>=M43) and found that the SumProduct selects the same "far too many" results whatever the OR statement contains ! I had thought that in this latter example <range>=M43 would be the same as OR(<range>=M43), but they are very different.

    Anyone know where I am going wrong ?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Further testing confirms that an OR statement within a SumProduct appears always to be TRUE.

    Is there another syntax which does what I want it to do ?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Further (simple !) research: obviously criterion1*criterion2 within a Sumproduct is that same (logically) as AND(criterion1,criterion2), so there is no need to use an AND function.

    BUT, if you do use an AND function within a SumProduct, it turns out to be always FALSE (just is an OR function is always TRUE).

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Solved !!

    Well, sort of.

    I hadn't realised that I could use a + operator within a SumProduct function. This roughly simulates an OR function.

    Criterion1+Criterion2 is not exactly the same as OR(Criterion1,Criterion2) (because, if both criteria are true, the former result is 2 whereas the OR function gives 1) but in practice they are the same since the one item being tested can never meet both criteria.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Final posting (by me) in this self-solving thread !

    The exact equivalent of an OR function with multiple criteria, that works within a SUMPRODUCT function, is:

    SUMPRODUCT( ( (Criterion1+Criterion2+Criterion3 . . . .) >0 )* other criteria).

    If any criterion in the first group is TRUE, the (sum of criteria)>0 statement (which works within SUMPRODUCT) returns TRUE, just as and OR function would.

    I suppose this was already "known" - just not by me

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you can also use SIGN:

    SUMPRODUCT( SIGN (Criterion1+Criterion2+Criterion3 . . . .)* other criteria)

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    That IS neater, thanks.

    It is strange that so many functions do work, but not OR or AND.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It is strange that so many functions do work, but not OR or AND.
    In ways it is not strange since AND and OR work directly on Arrays and are not individual functions. When working with the array functions we want to work on individual items which form an array at the end and then do a function at the end on the array.

    AND and OR work on the intermediate arrays first and then the final function, instead of having an array works on an single response. If you use AND or OR the single response is either TRUE or FALSE.

    So it is not a matter of them not working, as it is that they work in a way that you are not trying to achieve.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you. I am beginning to get my head around the logic you kindly have laid out.

    SIGN however is a very satisfactory route to "any of these", which is what I was looking for.

Posting Permissions

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