Performance of Formulas for Unique Count

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
What follows is triggered by a recent thread on unique count:

http://www.mrexcel.com/board2/viewtopic.php?t=295052
Performance of Formulas for Unique Count.xls
ABCDEF
1{=COUNTDIFF(UPPER(A2:A13225),TRUE,"")}
2Calc Time(Millisec)%BookMicroSecs
3Re CalcFull CalcVolatileOvHead/Formula
40.057.10.1%5.257,093.6
50.048.90.1%None48,939.7
60.048.80.1%None48,771.8
7MEDIAN0.048.90.1%515.9%48,939.7
8
9{=SUM(IF(A2:A13225<>"",1/COUNTIF(A2:A13225,A2:A13225)))}
10Calc Time(Millisec)%BookMicroSecs
11Re CalcFull CalcVolatileOvHead/Formula
120.08,537.20.0%75.98,537,211.3
130.08,524.50.0%None8,524,544.3
140.08,417.50.0%15.68,417,450.5
15MEDIAN0.08,524.50.0%45.78,524,544.3
16
17=SUMPRODUCT((A2:A13225<>"")/COUNTIF(A2:A13225,A2:A13225&""))
18Calc Time(Millisec)%BookMicroSecs
19Re CalcFull CalcVolatileOvHead/Formula
200.08,390.30.0%None8,390,289.5
210.08,394.90.0%1.38,394,944.9
220.08,500.70.0%14.88,500,686.0
23MEDIAN0.08,394.90.0%8.18,394,944.9
24
25{=SUM(IF(COUNTIF(A2:A13225,A2:A13225)=0,"", 1/COUNTIF(A2:A13225,A2:A13225)))}
26SheetsCalc Time(Millisec)%BookMicroSecs
27CountRe CalcFull CalcVolatileOvHead/Formula
2830.016,890.60.0%85.616,890,555.1
2930.016,938.90.0%None16,938,900.1
3030.016,937.90.0%None16,937,873.7
31MEDIAN0.016,937.90.0%85.616,937,873.7
32
33{=SUM(IF(FREQUENCY(IF(A2:A13225<>"", MATCH("~"&A2:A13225,A2:A13225&"",0)), ROW(A2:A13225)-ROW(A2)+1),1))}
34Calc Time(Millisec)%BookMicroSecs
35Re CalcFull CalcVolatileOvHead/Formula
360.0677.30.0%0.1677,286.6
370.0677.60.0%0.8677,571.3
380.0676.80.0%None676,776.0
39MEDIAN0.0677.30.0%0.4677,286.6
FastXL


As the above timing results with FastExcel based on three runs show, the formula with COUNTDIFF is the winner, the one with FREQUENCY/MATCH (a recent addition to the set of formulas for unique/distinct count) the second best. The formula which is apparently posted on "JWalk" site, that is,

{=SUM(IF(COUNTIF(A2:A13225,A2:A13225)=0,"",1/COUNTIF(A2:A13225,A2:A13225)))}

is the worst performer.

Note that COUNTDIFF is case-senstive. That is why it includes UPPER to simulate the others.

Note also that the CountDiff formula will yield correct a correct result in face of special meaning chars like<, *, ? that could figure in strings. The formulas with CountIf won't. The formula with Frequency/Match while less sensitive to such chars, it's not immune against trailing "*".
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
That's great! It's nice to have some hard data comparing the efficiency rate between the formulas. And, I had not realized that COUNTDIFF is case-sensitive. Thanks Aladin!
 
Upvote 0
This is cool!
Are there any performance differences between an ArrayFormula and just a Formula (or any different restrictions)?
 
Upvote 0
This is cool!
Are there any performance differences between an ArrayFormula and just a Formula (or any different restrictions)?

Formulas processing range objects (ex. SumIf) are generally (but not always) faster than their counterparts processing array objects (ex. SumProduct or Sum\If)... Another performance affecting issue arises from the use of volatile functions like Indirect, Offset, etc.

See also: www.decisionmodels.com/
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top