Counting Unique Values by Column - V LArge Dataset

Hi,

Because of this:

Originally Posted by PeterkMiles

The column purely contains numbers

instead of this:

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

which is in any case a notoriously slow construction, you can use:

=SUMPRODUCT(0+(FREQUENCY(Range,Range)>0))

Even if we were talking about non-numerics, a FREQUENCY/MATCH set-up would still perform far, far better than the equivalent SUMPRODUCT/COUNTIF one, which, in my opinion, really should have been cast upon the scrapheap of redundant Excel constructions a long time back.

Regards

Originally Posted by XOR LX

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))

which is in any case a notoriously slow construction

Agree, but that construct would be OK on "small" ranges.

Just did a quick test on 65535 rows of numbers.

=SUM(IF(FREQUENCY(A1:A65535,A1:A65535)>0,1)) (Normally entered, doesn't need CSE)

=SUM(--(FREQUENCY(A1:A65535,A1:A65535)>0))

=SUMPRODUCT(0+(FREQUENCY(A1:A65535,A1:A65535)>0))

All 3 formulas averaged 6.5 secs to calculate.

Thanks, Tony.

Yes - perhaps with small ranges the SUMPRODUCT/COUNTIF set-up does not perform significantly worse than the equivalent FREQUENCY/MATCH one. But then why get into a bad habit?

Also, you're correct that here SUMPRODUCT is not necessary - simply SUM will suffice.

Regards

Hi,

From my knowledge the SUM FREQUENCY option works fine but also has some calculation time.
If you need the actual speed (let's say within 1 or 2 seconds) you need to switch to a VBA solution.

Try it like this...

=SUM(--(FREQUENCY(B2:B91376,B2:B91376)>0))

Originally Posted by PeterkMiles

I must be doing something wrong, when using =SUMPRODUCT(0+(FREQUENCY(B2,B91376)>0))

You appear to be using just a single cell (B2) for the first argument.

Have a look at my answer again:

Originally Posted by XOR LX

=SUMPRODUCT(0+(FREQUENCY(Range,Range)>0))

Regards