I have enjoyed XLD's sticky posts (parts 1-3) on the SUMPRODUCT function.
Me too.


The way I look at array formulas, is like a VBA For/Next loop: (rightly or wrongly)

=SUMPRODUCT(--(MyContacts!$B$1:$B$14={1968,1969,1970,1971,1972,1973,1974,1975,"Unknown"  }))
For i = 1 to 14
    If Bi = 1968 then Add 1
Next i
answer so far = 1
For i = 1 to 14
    If Bi = 1969 then Add 1
answer = 2
Next I
etc.

When done, the anwser is 9 (correct)

Applying the same logic to the inverse case ...

=SUMPRODUCT(--(MyContacts!$B$1:$B$14<>{"Teacher","","NA",1967,1976}))
For i = 1 to 14
    If Bi <> 1968 then Add 1
Next i
answer so far = 13
For i = 1 to 14
    If Bi = "" then Add 1
answer = 26
Next i
etc.

When done, the answer is 65 (not correct)

Of the 14 values, 13 are not = each of the 5 conditions, so 5 x 13 = 65

At least that's the way I look at it

Maybe someone can come up with a clever formula so you won't need to test each case with a seperate SUMPRODUCT

Paul