Help needed with SUMPRODUCT please

Using Excel 2003 on XP.

I am trying to get a result based on values in 3 columns. Column J contains various dates, Column K contains various Employee group codes, Column T contains various card type codes.

I have only recently begun using the SUMPRODUCT function and am still trying to learn this (thanks to rylo for the introduction) but the latest formula I have constructed is giving me a #VALUE! error and I can't see what I'm doing wrong. I have Googled a number of sites (including this one) for information on using SUMPRODUCT but am still unable to see my error.

I have named the ranges I am using. The formula is:

=SUMPRODUCT(--(Org_End=DATEVALUE("31/12/9999")),--(EEGrp={"0","1","2","3","7","8"}),--(CType<>{"1","2"}))

The named ranges are:
Org_End: =Sheet1!$J$1:$J$886
EEGrp: =Sheet1!$K$1:$K$886
CType: =Sheet1!$T$1:$T$886

What I am trying to get is:
If column J contains the date 31/12/9999, AND column K contains the Employee group codes of 0, 1, 2, 3, 7, or 8, AND Column T contains any card type OTHER THAN 1 or 2, I need a positive result.

I have used the "Trace Error" tool and it points me to the top cell in each range i.e. J1, K1, and T1.

Am I incorrectly using the double unary? Is SUMPRODUCT the best function for this scenario or should I be using something else?

Any assistance showing where I am going wrong would be greatly appreciated.

Have you seen this link on SUMPRODUCT, may be it can throw some light on it for you?

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

oldchippy,

Thanks for the link . Yes, it is one of the sites I have used to try to better understand the use of SUMPRODUCT. I was simply missing something with the formula I constructed.

daddylonglegs,

Thanks for the amended formula (the date I am identifying in Org_End really has the year as 9999 as this indicates a "permanent" employee). I changed your formula to this but am getting a result of 318 (regardless of the date in Org_End). I was expecting to see either 0 for False or 1 for True. The Ctype section of your formula also seems to suggest I am trying to Match 1 or 2 (am I reading that right?). I actually want a True response if CType is OTHER THAN 1 or 2.

I'll keep playing to try to see where I am going wrong.

[EDIT: I'm sure I have incorrectly placed values in an array which shouldn't be there. Going off to study more ...]

I've been playing but getting nowhere fast.

If I break the SUMPRODUCT down into three discrete sections:
=SUMPRODUCT(--(Org_End=DATEVALUE("31/12/9999")))
=SUMPRODUCT(--(EEGrp={0,1,2,3,7,8}))
=SUMPRODUCT(--(CType<>(1)),--(CType<>2))
I get the correct sum returned for each one but that isn't what I need. I need a TRUE/FALSE result based on all my conditions and I'm just not "getting" it. When I lump these three conditions together:
=SUMPRODUCT(--(Org_End=DATEVALUE("31/12/9999")),--(EEGrp={0,1,2,3,7,8})--(CType<>("1")),--(CType<>"2")), I still get #VALUE!.

If a record has the date 31/12/9999, AND has an employee group code of 0, 1, 2, 3, 7, or 8, AND has a card code of 1 or 2, their data all correlates and I don't need to know any more about them. However, if their card code is anything OTHER THAN 1 or 2, I need these flagged for further investigation. What am I doing wrong? Slowly going bald ...