Counting by Two Columns of Variables
 

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?

Counting by Two Columns of Variables
 

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?

Counting by Two Columns of Variables
 

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

"Paul C" wrote:

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?

Counting by Two Columns of Variables
 

Try something like this...

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

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
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()?

"Paul C" wrote:

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?

Counting by Two Columns of Variables
 

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

"T. Valko" wrote:

Try something like this...

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

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
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()?

"Paul C" wrote:

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?


.

Counting by Two Columns of Variables
 

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

"Mashuganah" wrote in message
...
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

"T. Valko" wrote:

Try something like this...

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

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
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()?

"Paul C" wrote:

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?


.

Counting by Two Columns of Variables
 

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


"Mashuganah" wrote in message
...
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

"T. Valko" wrote:

Try something like this...

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

--
Biff
Microsoft Excel MVP


"Mashuganah" wrote in message
...
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()?

"Paul C" wrote:

add a column to the call data and use this formula in c2
=if(iserror(match(A2,Stafflistrange,0)),"No",if(is error(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
--
If this helps, please remember to click yes.


"Mashuganah" wrote:

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?


.

Counting by Two Columns of Variables
 

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

Counting by Two Columns of Variables
 

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

Counting by Two Columns of Variables
 

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