- Joined
- May 15, 2008
- Messages
- 2
- Reaction score
- 0
Hello,
I'm using MS Office Excel 2003. I hope this makes sense but I have two sheets, one with the detail and one I need a count filled in. I need a count of accounts for each Unit ID (Sales ID). My problem is the account id may be duplicated in the detail sheet but I only want it to count once per Unit ID (Sales ID). I tried using the following array =SUM(IF(FREQUENCY(Andy!E2:E1129,Andy!E2:E1129)>0,1)) but I need an If...Then statement in there somewhere like IF(A2=Andy!A2:A11 then SUM(IF(FREQUENCY(Andy!E2:E1129,Andy!E2:E1129)>0,1)).
Sales IDAssociateManager# of Accts# of SeqWBC003Jeremy EssexAndy Bornowski1128WBC005Julie FrahmAndy Bornowski1066WBC006Luke BirdAndy Bornowski893
Andy Sheet (Detail)
unit_idAssociateManagerrep_idaccount_idWBC003Jeremy EssexAndy BornowskiC20700B22416300WBC003Jeremy EssexAndy BornowskiC20700B 24792270WBC003Jeremy EssexAndy BornowskiC20700B 25039635WBC003Jeremy EssexAndy BornowskiC20700B 27016920
Thank you for any help!
Elizabeth
I'm using MS Office Excel 2003. I hope this makes sense but I have two sheets, one with the detail and one I need a count filled in. I need a count of accounts for each Unit ID (Sales ID). My problem is the account id may be duplicated in the detail sheet but I only want it to count once per Unit ID (Sales ID). I tried using the following array =SUM(IF(FREQUENCY(Andy!E2:E1129,Andy!E2:E1129)>0,1)) but I need an If...Then statement in there somewhere like IF(A2=Andy!A2:A11 then SUM(IF(FREQUENCY(Andy!E2:E1129,Andy!E2:E1129)>0,1)).
Sales IDAssociateManager# of Accts# of SeqWBC003Jeremy EssexAndy Bornowski1128WBC005Julie FrahmAndy Bornowski1066WBC006Luke BirdAndy Bornowski893
Andy Sheet (Detail)
unit_idAssociateManagerrep_idaccount_idWBC003Jeremy EssexAndy BornowskiC20700B22416300WBC003Jeremy EssexAndy BornowskiC20700B 24792270WBC003Jeremy EssexAndy BornowskiC20700B 25039635WBC003Jeremy EssexAndy BornowskiC20700B 27016920
Thank you for any help!
Elizabeth