Evaluate Array Formula Issue

Hi guys,

I have a list of numbers in a column in Excel. Some, however, have been entered as text whilst others are 'true' numbers and so I use an array formula to sum the column as follows;

={SUM(IFERROR(VALUE(iData!A2:A50),0))}

Note: the IFERROR is in there as there are also cells with other random text in that the VALUE function does not like so I'm ignoring those and resolving to 0).

My query is; if I enter the following in VBA,

Total = Evaluate("=SUM(IFERROR(VALUE(iData!A2:A50),0))") then my new variable 'Total' is only summing the numeric cells in the range A2:A50....the numbers entered as text aren't being recognised??

My understanding was that the two methods should do the same thing, no?

Hey Bernie

I've had another look and it appears that the troublesome text values have a '£' prefix. So my VBA statement seems to work in summing 2,5, & '5' to equal 12 but only returns 7 when summing 2,5 & '£5'. The spreadsheet formula doesn't seem to mind the currency prefix...

Aah...that's sorted it

Thank you!!