Improvement

Instead of adding NOT, just replace ISNUMBER with ISNA:

=SUMPRODUCT(--(ISNA(MATCH(A1:A10,Staff!A1:A10,0))),--(ISNUMBER(MATCH(B1:B10,VIP!A1:A10,0))))

Or:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,Staff!A1:A10,0))),--(ISNA(MATCH(B1:B10,VIP!A1:A10,0))))

Does the same thing as adding NOT but saves a few keystrokes.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Whichever condition you want to exclude just add NOT in front of
ISNUMBRER:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(A1:A10,Staff!A1:A10,0)))),--(ISNUMBER(MATCH(B1:B10,VIP!A1:A10,0))))

Or:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,Staff!A1:A10,0))),--(NOT(ISNUMBER(MATCH(B1:B10,VIP!A1:A10,0)))))

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
Thanks Biff. When I looked at your sample I realized that I had entered
one
of the parameters incorrectly. The equation works.

My remaining question is how to alter the equation to count all records
that
match one variable list but do NOT match a second list of variables.
That
is, just like the current equation but with one comparison being
exclusive
rather than both comparisons being inclusive.

Greg

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xMashuganah.xls 16 kb

http://cjoint.com/?mvsnH3AQ51

I put everything on one sheet so that it will be easier to see.

--
Biff
Microsoft Excel MVP