Counting by Two Columns of Variables

  • Thread starter Thread starter Mashuganah
  • Start date Start date
M

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?
 
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
 
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()?
 
Try something like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,Staff!A1:A10,0))),--(ISNUMBER(MATCH(B1:B10,VIP!A1:A10,0))))
 
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
 
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)))
 
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
 
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)))))
 
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.
 
Back
Top