Catch Array Formula in a variable

This is the formula :

Please Login or Register  to view this content.

Question now is how will I catch the result of this formula into a variable to use in a macro.

example:

A = 'outcome of above formula'

Please Login or Register  to view this content.

ps: I'm assuming I can make the endrow of column X variable in VBA. So the solution would also need to work if lets say X4922 would be ":X" & lrow.

try untested

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Hi nigelog,
Thanks but when I paste :

Please Login or Register  to view this content.

into the editor I'm getting the 'Compile Error' 'Expected Expression' error.

You had an Application.worksheet funtion which is probably different from an array formula which this SUM/FREQUENCY formula is.

Ok, but this alone will not do it correct?

Because trying your solution nitwit gives and error too if i do msgbox A (because A - error2902)

You are correct, my bad!

Thank you very much!