Counting by Two Columns of Variables

[Mashuganah]

I have three worksheets: call data, staff, and VIPs. The staff and VIP
worksheets each contain only a single column of names. The call data sheet
contains a list of staff names and caller names along with call data. I need
to count the number of calls (records) in which the staff name matches one of
those on the staff sheet AND the caller name matches one of those on the VIP
sheet.

I've tried playing around with COUNT and MATCH to no avail. How would I
perform such a count?

 

[Paul C]

add a column to the call data and use this formula in c
=if(iserror(match(A2,Stafflistrange,0)),"No",if(iserror(match(B2,VIPlistrange,0)),"No","Yes"))

and then count Yes in Column C. You will get the total and each matching
call will be marked.

A B C
Staff name Caller Name
John Smith Mr VIP

 

[Mashuganah]

Thanks Paul. That works. However, I have more than 15K records and am
wondering whether it's possible to include the count of "yes" or "no" in a
single equation on a report sheet. That is, to bulk up the equation you
posted to include a count. Could the whole thing be enveloped in a countif()?

 

[T. Valko]

Try something like this...

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

 

[Mashuganah]

Valko,

That equation returns zero (i.e., FALSE). I'm not well enough versed in the
combination of sumproduct and isnumber to figure out what might be wrong. Do
you have suggestions?

Greg

 

[David Biddulph]

Why not do the usual thing if you are struggling with a long formula? Split
it, and try each part separately?
=SUMPRODUCT(--ISNUMBER(MATCH(A1:A10,Staff!A1:A10,0)))
=SUMPRODUCT(--ISNUMBER(MATCH(B1:B10,VIP!A1:A10,0)))

 

[T. Valko]

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.

 

[Mashuganah]

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]

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)))))

 

[T. Valko]

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.