Sir Babydum GBE
01-23-2008, 05:53 AM
Hi
Column A contains a list of agents, column B contains the number of sales they've made. I got a formula from the net to return the number of unique agents in a list:=SUM(IF(LEN(MyAgents),1/COUNTIF(MyAgents,MyAgents)))(MyAgents is a dynamic range)
What I also need is to count the number of unique agents that made at least 1 sale. So I tried this:=IF(LEN(MyAgents),SUMPRODUCT((1/COUNTIF(MyAgents,MyAgents)*(MySales>0))))It sort of works - but not exactly. Say "Babydum" was on the list twice, and on only one occurence there was a sale - I would want the result of the above formula to be 1 - but I get 0.5.
Any ideas please?
Column A contains a list of agents, column B contains the number of sales they've made. I got a formula from the net to return the number of unique agents in a list:=SUM(IF(LEN(MyAgents),1/COUNTIF(MyAgents,MyAgents)))(MyAgents is a dynamic range)
What I also need is to count the number of unique agents that made at least 1 sale. So I tried this:=IF(LEN(MyAgents),SUMPRODUCT((1/COUNTIF(MyAgents,MyAgents)*(MySales>0))))It sort of works - but not exactly. Say "Babydum" was on the list twice, and on only one occurence there was a sale - I would want the result of the above formula to be 1 - but I get 0.5.
Any ideas please?