software

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)
--
Nicki Taylor

Both you formulas work for me.

--
HTH

Sandy
with @tiscali.co.uk

quot;NTaylorquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I have a formula (see below) that will work for rows 2-1000, but once I
gt; change it to look for columns beyond 2000, it will not work anymore... Any
gt; suggestions?
gt;
gt; This works:
gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
gt; Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)
gt;
gt; This DOESN'T work:
gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
gt; Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
gt;
gt;
gt;
gt; --
gt; Nicki Taylor
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
--
Nicki Taylorquot;Sandy Mannquot; wrote:

gt; Both you formulas work for me.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt; quot;NTaylorquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; I have a formula (see below) that will work for rows 2-1000, but once I
gt; gt; change it to look for columns beyond 2000, it will not work anymore... Any
gt; gt; suggestions?
gt; gt;
gt; gt; This works:
gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
gt; gt; Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)
gt; gt;
gt; gt; This DOESN'T work:
gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
gt; gt; Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Nicki Taylor
gt;
gt;
gt;

Not really, what exactly do you mean by:

gt;gt; gt; This DOESN'T work:

what return do you get? Error message? Zero? Wrong sum? What?

--
Regards

Sandy
with @tiscali.co.ukquot;NTaylorquot; gt; wrote in message
...
gt; Do you know why mine wouldn't? It looks like it will only go as far as
gt; row
gt; 4000, and then the formula doesn't work any more. Does mine have a limit
gt; or
gt; something?
gt;
gt; Thanks
gt; --
gt; Nicki Taylor
gt;
gt;
gt; quot;Sandy Mannquot; wrote:
gt;
gt;gt; Both you formulas work for me.
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt; quot;NTaylorquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello,
gt;gt; gt;
gt;gt; gt; I have a formula (see below) that will work for rows 2-1000, but once I
gt;gt; gt; change it to look for columns beyond 2000, it will not work anymore...
gt;gt; gt; Any
gt;gt; gt; suggestions?
gt;gt; gt;
gt;gt; gt; This works:
gt;gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
gt;gt; gt; Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)
gt;gt; gt;
gt;gt; gt; This DOESN'T work:
gt;gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
gt;gt; gt; Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Nicki Taylor
gt;gt;
gt;gt;
gt;gt;
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.
Editgt;Copy
Select your quot;numbersquot;
Editgt;Paste special, check Multiply

--
Kind regards,

Niek Otten

quot;NTaylorquot; gt; wrote in message
...
gt; Do you know why mine wouldn't? It looks like it will only go as far as
gt; row
gt; 4000, and then the formula doesn't work any more. Does mine have a limit
gt; or
gt; something?
gt;
gt; Thanks
gt; --
gt; Nicki Taylor
gt;
gt;
gt; quot;Sandy Mannquot; wrote:
gt;
gt;gt; Both you formulas work for me.
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt; quot;NTaylorquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello,
gt;gt; gt;
gt;gt; gt; I have a formula (see below) that will work for rows 2-1000, but once I
gt;gt; gt; change it to look for columns beyond 2000, it will not work anymore...
gt;gt; gt; Any
gt;gt; gt; suggestions?
gt;gt; gt;
gt;gt; gt; This works:
gt;gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
gt;gt; gt; Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)
gt;gt; gt;
gt;gt; gt; This DOESN'T work:
gt;gt; gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
gt;gt; gt; Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; Nicki Taylor
gt;gt;
gt;gt;
gt;gt;
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.
--
Nicki Taylorquot;NTaylorquot; wrote:

gt; Hello,
gt;
gt; I have a formula (see below) that will work for rows 2-1000, but once I
gt; change it to look for columns beyond 2000, it will not work anymore... Any
gt; suggestions?
gt;
gt; This works:
gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$1000= 'Per Peice'!A9),'Q
gt; Info'!$C$2:$C$1000*'Q Info'!$B$2:$B$1000)
gt;
gt; This DOESN'T work:
gt; =SUMPRODUCT(--('Q Info'!$A$2:$A$9999= 'Per Peice'!A9),'Q
gt; Info'!$C$2:$C$9999*'Q Info'!$B$2:$B$9999)
gt;
gt;
gt;
gt; --
gt; Nicki Taylor