Hi Vito,
I don't really know how the VBA Evaluate function knows array formulas when they're passed to it (it doesn't need the { } brackets) - but it does!! Try this:
Sub test()
Dim ws As Worksheet
Dim x As Variant
Set ws = ActiveSheet
'This way it returns the correct result but puts it into the cell
ws.Range("A1").FormulaArray = "=sum(1/countif(b4:b142,b4:B142))"
'I would like to store the result straight into a variable
'x = FormulaArray = "=sum(1/countif(B4:B142,B4:B142))"
x = Evaluate("SUM(1/countif(B4:B142,B4:B142))")
MsgBox x
End Sub
You should try and avoid array formulas where possible as they're a big drain on resources. For some other possible formulas to count the number of unique entries see here.
HTH
Robert
Bookmarks