Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
won't calculate far enough
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
won't calculate far enough
Both you formulas work for me.
-- HTH Sandy with @tiscali.co.uk "NTaylor" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
won't calculate far enough
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 Taylor "Sandy Mann" wrote: Both you formulas work for me. -- HTH Sandy with @tiscali.co.uk "NTaylor" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
won't calculate far enough
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. EditCopy Select your "numbers" EditPaste special, check Multiply -- Kind regards, Niek Otten "NTaylor" wrote in message ... 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 Taylor "Sandy Mann" wrote: Both you formulas work for me. -- HTH Sandy with @tiscali.co.uk "NTaylor" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
won't calculate far enough
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 Taylor "NTaylor" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate hours in Excel | New Users to Excel | |||
Spreadsheet Won't Calculate | Excel Discussion (Misc queries) | |||
help with sumif to calculate column | Excel Discussion (Misc queries) | |||
How can I calculate Vacation Time earned based on length of emplo. | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) |