Count If 2 Diff Values Appear on Several Worksheets w/in Workbook
 

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!

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!

In your range you need to select one sheet at a time. You might have to
do a countif for each sheet and then add them all up.


--
BlueDaze


------------------------------------------------------------------------
BlueDaze's Profile: http://www.excelforum.com/member.php...o&userid=27465
View this thread: http://www.excelforum.com/showthread...hreadid=469781

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!

Thank you for your reply. Thats what I have done in the meantime, but I
really need one cell total for every time Lenny appears in Column H and if
Kat or Pam appear in Column K on the same row. I think I have a punctuation
error somewhere in my previous formula and I also think the *AND is wrong
=COUNTIF((Green!:Montana!H2:H500,"Lenny")*AND(Gree n!:Montana!K2:K500,"kat"))
Any other suggestions? Thanks!

"BlueDaze" wrote:


In your range you need to select one sheet at a time. You might have to
do a countif for each sheet and then add them all up.


--
BlueDaze


------------------------------------------------------------------------
BlueDaze's Profile: http://www.excelforum.com/member.php...o&userid=27465
View this thread: http://www.excelforum.com/showthread...hreadid=469781

Hi!

I cant put a formula in each cell where Lenny appears and
then create a Sum formula.

You don't need a formula in each cell where Lenny appears!

You use 1 formula on each sheet that counts the criteria on that sheet then
you sum that cell on all the sheets. If you have 5 sheets then you would
have a total of 6 formulas. 1 per sheet and 1 to sum.

Did you try what I suggested? Try it, it works!

Biff

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