Sumproduct on multiple criteria

[technik]

Hi

Apologies if someone has answered this before but I think it's something simple but isn't working for me.

I have three columns of data (scores like 1.2, 4.3, 0, 5.6 etc. etc.) in each of the columns B,C,D on sheet 2. (Column A has the Candidate Name).

I am doing a count of the number of scores that are greater than 1 in columns B and C or D.

I can do the count of the number of scores that are greater than 1 in columns B and C using the code below and I think it's working correctly. However I need to check Column B score with Column C OR D ie. a count of the number that are getting a score of >1 in column B AND >1 in (column C OR D) .... ie if someone scored >1 in column B but <1 in column C but >1 in column D, then they would include in the count (or vice versa for columns C/D).

Please Login or Register  to view this content.

I tried adding AND(OR( to the sumproduct but it's not quite working right.

Can anyone help?

Thanks

[AliGW]

Better to attach the workbook so that we can troubleshoot.

[Fluff13]

How about

Please Login or Register  to view this content.

[divinedeba]

can you please attach the screenshot of the table

[AliGW]

Thanks for your interest, Divinedeba - we don't encourage the use of screenshots, as they are of little practical use. I have already requested a workbook.

[technik]

Thanks Fluff13

That doesn't work as I get an answer of 122 but only have 70 rows(!)

AliGW - I have attached the file .. thanks for your help in advance.

[XLent]

you need to either do a >0 test on the OR -- or, simpler still, wrap the OR (+) within a SIGN to enforce 0-1 result as opposed to 0-2

=SUMPRODUCT((Sheet2!$B$2:$B$70>=1)*SIGN((Sheet2!$C$2:$C$70>=1)+(Sheet2!$D$2:$D$70>=1)))

[technik]

Do you mean

Please Login or Register  to view this content.

??

I still don't get the correct answer with this I think

[XLent]

No, my prior post contains the correct syntax.

In SUMPRODUCTs, like a CSE Array, ORs are conducted by means of Addition - ANDs are conducted via multiplication, e.g.

(test1)+(test2) is an OR

(test1)*(test2) is an AND

the issue with the OR syntax is, unless mutually exclusive tests, is that you can over count - i.e. your OR could return 2 if both tests returned True

to negate the over counting, ie. to prevent 2, you can apply either a SIGN call (0 for 0, 1 for >0)

SIGN((test1)+(test2))

or you could do a >0 test

(((test1)+(test2))>0)

IMO the SIGN approach is always simpler, syntax wise.

[technik]

Thanks XLent - think that's done the trick.

Much appreciated. Love the SIGN approach and didn't know it even existed.