Performance of Formulas for Unique Count

[Aladin Akyurek]

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
	A	B	C	D	E	F
1	{=COUNTDIFF(UPPER(A2:A13225),TRUE,"")}
2		Calc Time(Millisec)		%	Book	MicroSecs
3		Re Calc	Full Calc	Volatile	OvHead	/Formula
4		0.0	57.1	0.1%	5.2	57,093.6
5		0.0	48.9	0.1%	None	48,939.7
6		0.0	48.8	0.1%	None	48,771.8
7	MEDIAN	0.0	48.9	0.1%	515.9%	48,939.7
8
9	{=SUM(IF(A2:A13225<>"",1/COUNTIF(A2:A13225,A2:A13225)))}
10		Calc Time(Millisec)		%	Book	MicroSecs
11		Re Calc	Full Calc	Volatile	OvHead	/Formula
12		0.0	8,537.2	0.0%	75.9	8,537,211.3
13		0.0	8,524.5	0.0%	None	8,524,544.3
14		0.0	8,417.5	0.0%	15.6	8,417,450.5
15	MEDIAN	0.0	8,524.5	0.0%	45.7	8,524,544.3
16
17	=SUMPRODUCT((A2:A13225<>"")/COUNTIF(A2:A13225,A2:A13225&""))
18		Calc Time(Millisec)		%	Book	MicroSecs
19		Re Calc	Full Calc	Volatile	OvHead	/Formula
20		0.0	8,390.3	0.0%	None	8,390,289.5
21		0.0	8,394.9	0.0%	1.3	8,394,944.9
22		0.0	8,500.7	0.0%	14.8	8,500,686.0
23	MEDIAN	0.0	8,394.9	0.0%	8.1	8,394,944.9
24
25	{=SUM(IF(COUNTIF(A2:A13225,A2:A13225)=0,"", 1/COUNTIF(A2:A13225,A2:A13225)))}
26	Sheets	Calc Time(Millisec)		%	Book	MicroSecs
27	Count	Re Calc	Full Calc	Volatile	OvHead	/Formula
28	3	0.0	16,890.6	0.0%	85.6	16,890,555.1
29	3	0.0	16,938.9	0.0%	None	16,938,900.1
30	3	0.0	16,937.9	0.0%	None	16,937,873.7
31	MEDIAN	0.0	16,937.9	0.0%	85.6	16,937,873.7
32
33	{=SUM(IF(FREQUENCY(IF(A2:A13225<>"", MATCH("~"&A2:A13225,A2:A13225&"",0)), ROW(A2:A13225)-ROW(A2)+1),1))}
34		Calc Time(Millisec)		%	Book	MicroSecs
35		Re Calc	Full Calc	Volatile	OvHead	/Formula
36		0.0	677.3	0.0%	0.1	677,286.6
37		0.0	677.6	0.0%	0.8	677,571.3
38		0.0	676.8	0.0%	None	676,776.0
39	MEDIAN	0.0	677.3	0.0%	0.4	677,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 "*".

 

[Domenic]

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!

 

[memar_one]

This is cool!
Are there any performance differences between an ArrayFormula and just a Formula (or any different restrictions)?

 

[Aladin Akyurek]

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/