# Thread: Use of OR within a Sumproduct

1. 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. 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. 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. 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. 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. FWIW, you can also use SIGN:

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

7. That IS neater, thanks.

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

8. 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. 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
•