PDA

View Full Version : conditionally return number of Unique entries



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?

Bob Phillips
01-23-2008, 06:10 AM
=COUNT(1/IF(MySales<>"",MATCH(MyAgents,MyAgents,0)=ROW(MyAgents)-MIN(ROW(MyAgents))+1))

again an array formula

Sir Babydum GBE
01-23-2008, 06:16 AM
=COUNT(1/IF(MySales<>"",MATCH(MyAgents,MyAgents,0)=ROW(MyAgents)-MIN(ROW(MyAgents))+1))

again an array formula

Thanks XLD !

I must investgate MATCH - I never use it.

Bob Phillips
01-23-2008, 06:32 AM
I probably over-use it, it is one of my staple VBA tools as well.

Sir Babydum GBE
01-23-2008, 07:48 AM
Ok, sorry I should be able to work this out... But suppose I want to add a third condition - so lets say I need to find a unique agent who has sales and works in Cardiff. If MySite is the column with the office locations - could that be built in?

Bob Phillips
01-23-2008, 08:05 AM
I don't know. All this tuition, and you can't work out something as simple as this, I despair.

=COUNT(1/IF((MySales<>"")*(MySites="Cardiff"),MATCH(MyAgents,MyAgents,0)=ROW(MyAgents)-MIN(ROW(MyAgents))+1))

Sir Babydum GBE
01-23-2008, 08:10 AM
I don't know. All this tuition, and you can't work out something as simple as this, I despair.

I promise you - I despair twice as much as you over it.

Cheers

Bob Phillips
01-23-2008, 08:19 AM
Not to mention being in Cardiff!

Sir Babydum GBE
01-23-2008, 08:44 AM
Right, that's it! Where's my handbag???