"Bernie Deitrick" <deitbe @ consumer dot org> wrote...

...

As Charles Williams has pointed out in the past, there can be problems
passing multiple area ranges to udfs.

http://www.decisionmodels.com/calcsecretsj.htm

Besides, this doesn't require udfs if the values in the range are all
numeric. Using your example range, try

=COUNT(1/FREQUENCY((E4:E9,G6:G10,I6:I10),(E4:E9,G6:G10,I6:I10)))

For mixed text and numbers, more robust to use a udf that accepts a
variable number of arguments, though that would fail when one has more
than 30 ranges. Most robust would be to use a udf that returns the
distinct items in the entries in its argument ranges or arrays or the
arguments themselves when they're scalars, then call it nested
repeatedly and pass the result through COUNTA.

That is, a udf like

Function u(ParamArray a() As Variant) As Variant
 Dim d As Object, x As Variant, y As Variant, z As Variant

 Set d = CreateObject("Scripting.Dictionary")

 For Each x In a
   If TypeOf x Is Range Or IsArray(x) Then
     For Each y In x
       If TypeOf y Is Range Then z = y.Value Else z = y
       If Not d.Exists(z) Then d.Add Key:=z, Item:=1
     Next y
   ElseIf Not d.Exists(x) Then
     d.Add Key:=x, Item:=1
   End If
 Next x

 u = d.Keys

End Function

and call it like so

=COUNTA(u(E4:E9,G6:G10,I6:I10))

or

=COUNTA(u(u(A1,A3,...,A59),u(C1,C3,...,C59),u(E1,E3,...,E59)))