Thank you so much for your reply. Pls let me explain further - this is a
workbook of 5 worksheets documenting all calls made to customers by Kat and
Pam. We need to know how many of those calls ended in a referral to Lenny,
as Kat and Pam are paid on referrals. Ultimately, this report will contain
25000+ records, and only 10-20% of those calls will contain a Sales Rep
Referral name, so I cant put a formula in each cell where Lenny appears and
then create a Sum formula. I need a way to continually track when Lenny's
name is entered anywhere in Column H on any sheet, and if that entry is Kat's
call or Pam's call, noted in Column K on each sheet. I am pretty sure I am
just missing a punctuation mark of some kind in my previous formula
=COUNTIF((Green!:Montana!H2:H500,"Lenny")*AND(Gree n!:Montana!K2:K500,"kat"))
or the *AND is wrong. Does that make sense? Any other suggestions? Thanks!

"Biff" wrote:

Hi!

This would be much easier (and efficient) to do if you were to put a formula
in the same cell on each sheet then sum that cell.

Assume on sheet1 you have:

A1 = Lenny
B1 = Kat
C1 = Pam

On each of the other sheets in cell A1:

=SUMPRODUCT(--(H2:H500=Sheet1!A1),--(ISNUMBER(MATCH(K2:K500,Sheet1!B1:C1,0))))

Then back on Sheet1:

=SUM(Green:Montana!A1)

Biff

"Kat" wrote in message
...
I am trying to count how many times a Sales Rep's name appears and the
corresponding referral source within several worksheets on the same
workbook.
Always same columns (H and K) on each worksheet. First worksheet is
"Green"
Last worksheet is "Montana", Sales Rep name is Lenny and referral source
is
Kat or Pam. I am trying to put in
-COUNTIF((Green!:Montana!H2:H500,"Lenny")*AND(Green !:Montana!K2:K500,"kat"))
What am I doing wrong? Been so long since I learned this stuff...Any help
is much appreciated! Thanks!