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]

Is this related to the old thread?

https://www.mrexcel.com/forum/excel-questions/1097707-sumproduct-mid-multiple-sheets-not-working-2.html

Nobody can help you with what you have there until they know a few things.

What is the sumproduct formula?
Are there any value errors within the range A301:A1000?
What is contained within A301:A1000.

However i told you that in the last thread.

 

[Camel123]

Is this related to the old thread?

https://www.mrexcel.com/forum/excel-questions/1097707-sumproduct-mid-multiple-sheets-not-working-2.html

Nobody can help you with what you have there until they know a few things.

What is the sumproduct formula?
Are there any value errors within the range A301:A1000?
What is contained within A301:A1000.

However i told you that in the last thread.

Hi Steve, the formula is =SUMPRODUCT(--(A1:A1000)). No value errors in the range at all. A1-1000 contain numbers formatted as text.

 

[steve the fish]

Does A1 contain a header? For that to work all cells, even if text, need to be numbers.

 

[Marcelo Branco]

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?

I suspect some cells contain formulas that in some cases return "" and cause the #VALUE ! error.

M.

 

[Camel123]

No header.

 

[Camel123]

I suspect some cells contain formulas that in some cases return "" and cause the #VALUE ! error.

M.

There are formulas in 301-1000 yes.

 

[Marcelo Branco]

This array formula should work
=SUM(IF(ISNUMBER(--A1:A1000),--A1:A1000))
Ctrl+Shift+Enter

M.

 

[Camel123]

This array formula should work
=SUM(IF(ISNUMBER(--A1:A1000),--A1:A1000))
Ctrl+Shift+Enter

M.

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

 

[steve the fish]

Following on from your previous thread that isnt going to correct values such as $100, unless that is your local currency, where it cant coerce to number and will just ignore those. You may or may not have the result you need. The fact you had a formula that produced a value error tells me you need to find those cells and at least make a concious choice to ignore them.