SUMPRODUCT on numbers stored as text, issue

[Camel123]

Hi,

I use sumproduct on range A1:A300 for numbers stored as text. Works perfect. But I want to extend the range to A1:A1000 since row 301-1000 contain formulas and numbers might appear. When extending the range I get value error. How do I solve this?

 

[steve the fish]

Place this in B1:

=--A1

Drag it down. Look at the cells that are producing the value errors.

 

[Marcelo Branco]

Worked really good thank you all! Just to understand, can you explain the formula step by step?

You're welcome.

To see what the formula does, step by step, try Formulas > Evaluate Formula

M.

 

[Camel123]

Place this in B1:

=--A1

Drag it down. Look at the cells that are producing the value errors.

Please see function below. This one is in column AK. Doing the check you suggest on this column generates value errors on rows where 'forecast' sheet is missing data input.
=IFERROR(INDEX('forecast'!$AJ$3:$XFD$1048576;MATCH('all'!$M132;'forecast'!$B$3:$B$1048576;0);MATCH(AK$6;'forecast'!$AJ$2:$XFD$2;0))*1;"")