Connt Unique items - with No C+S+E

[Joe Galvan]

Hi all...

I need to be able to count unique items and overlook the duplicates....

I have a formula that can do that, but it is a rather intesive formula, so it takes for ever when you have 2000+ lines trying to calculate.

the formula I am currently using is

{=SUM(IF(FREQUENCY(IF($A$5:$A$10000<>"", IF($D$5:$D$10000=P6,MATCH("~"&$A$5:$A$10000,$A$5:$A$10000&"",0))),ROW($A$5:$A$10000)-ROW($A$5)+1),1))}

closed with C+S+E

Any way this could be a lighter formula? or a differnt formula can do the same thing? I am leaning towards an Index/match formula but just not sure...

thanks,
JOE

 

[Marcelo Branco]

That may even be worse.

By the time you copy that formula down to row 10,000 this is how many cells you've calculated:

=SUMPRODUCT(ROW(1:9996))

John and T.

Sorry, i replied too hastily.
I even did not see there was a condition...


M.