won't calculate far enough

N

NTaylor

Hello,

I have a formula (see below) that will work for rows 2-1000, but once I
change it to look for columns beyond 2000, it will not work anymore... Any
suggestions?

This works:
=SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)

This DOESN'T work:
=SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
 
N

NTaylor

Do you know why mine wouldn't? It looks like it will only go as far as row
4000, and then the formula doesn't work any more. Does mine have a limit or
something?

Thanks
 
N

Niek Otten

First you said 200, now 400. What makes you think so? What do you see?

I suspect the new rows are actually text, although they may look like
numbers and are formatted as numbers.
You can easily check with the ISTEXT() function.
If so:

Format an empty cell as Number.
Enter the number 1.
Edit>Copy
Select your "numbers"
Edit>Paste special, check Multiply
 
N

NTaylor

Thank you all.... Yes, Neik, you were right that I didn't notice a small
glitch that 5 of the 6500 rows were text. I have made the change. Thanks...
I thought I was going crazy.
 
Top