Why does this formula clog my spreadsheet?

[Guest]

After much searching, I have determined that the formula shown below is the
reason that recalculation on the named worksheet "All Data" takes up to 6
seconds. Does anyone know why this particular formula could be such a
problem? And if so, is there a substitution that would eliminate the issue?
Here's the formula:

=SUMPRODUCT(('All Data'!G4:L1504<>"")/COUNTIF('All Data'!G4:L1504,'All
Data'!G4:L1504&""))/COUNTIF('All Data'!E4:E1504,"Y")

TIA

 

[T. Valko]

I don't have an alternative solution but I can certainly see why that
formula is so expensive. I'm betting that most of the calc time is being
used in the first Countif. You're "searching" more than 9000 cells more than
9000 times.

Biff

 

[Guest]

A UDF may be faster than an array formula to determine the number of unique
values in your range.

If you are new to VBA, check here for more instructions
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Paste this code into a VBA code module.

Function Unique(rngData As Range, _
Optional blnCountBlanks As Boolean = False) As Double
Dim colUnique As Collection
Dim rngCell As Range

Set colUnique = New Collection

On Error Resume Next
For Each rngCell In rngData.Cells
If Len(rngCell.Value) > 0 Or _
(Len(rngCell.Value) = 0 And blnCountBlanks) Then _
colUnique.Add rngCell.Value, CStr(rngCell.Value)
Next rngCell

Unique = colUnique.Count

End Function


usage for your specific case would be
=unique('All Data'!G4:L1504)/COUNTIF('All Data'!E4:E1504,"Y")

to count blank cells as unique then set the optional second argument to true
=unique('All Data'!G4:L1504, TRUE)/COUNTIF('All Data'!E4:E1504,"Y")

 

[Charles Williams]

see http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com