Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count of Unique values | Excel Worksheet Functions | |||
getting values from other worksheet | Excel Worksheet Functions | |||
How do I find and replace "values" (like #N/A) in a worksheet? | Excel Discussion (Misc queries) | |||
Count Unique Values | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions |