Hi Anand,
What happens if you commit your formula as an array formula with Ctrl +
Shift + Enter.
I tried =SUM(VALUE(A1:A5)) when A1:A5 contained text numbers and it
only worked when commited as an array formula.

Hope this helps

Ken Johnson