Hi all,

I am trying to build a UDF that is the textual equivalent of FREQUENCY. The function would accept the same arguments as FREQUENCY and be array-entered over a range of cells of length one more than the length of the bins array (for 'other').

My function goes as follows:

Function c_frequency(data As range, bins As range) As Single()
nb = bins.Cells.Count
Dim results() As Single
ReDim results(1 To nb + 1)
Set entcol = data.EntireColumn
tot_rows = entcol.Rows.Count
' necessary code to adapt to my needs
Set newdata = range(entcol.Cells(range("collect").Row, 1), entcol.Cells(tot_rows, 1))
tot_found = 0
For i = 1 To nb
results(i) = WorksheetFunction.CountIf(newdata, bins(i))
tot_found = tot_found + results(i)
Next i
' count anything else
results(i) = WorksheetFunction.CountA(newdata) - tot_found
c_frequency = results
End Function

My problem: when I array-enter it, all cells have the value of result(1).

To test it, I define a name "collect" in, say, C4, and then below it enter random values with the formula
=IF(RAND()<0.5,"Heads","Tails")

Then I enter the values "Heads" and "Tails" in L8:L9 and next to them I am attempting on three cells:
=C_FREQUENCY(C:C,L8:L9)

But the returned results only reproduce the value of Heads in all three result cells.

What am I doing wrong?

TIA