Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
I have a massive spreadsheet, one of the worksheets has several thousand
rows, many columns. The sheet I am working on has about 200 rows. One of my columns is this: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2))} Works great when I reference H$1 or a "text value". When I move over a column: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2))} So, the only thing changed is =I$1. If I replace I$1 with "text" it works. I even tried retyping I1. I would rather reference since the text could change. Ideas. Is the spreadsheet simply too big? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
From the sounds of it ..
Try replacing this term in your expression: --('Raw Deviation'!$B$2:$B$6016=I$1) with this: --(TRIM('Raw Deviation'!$B$2:$B$6016)=TRIM(I$1)) P/s: Don't think your formula needs to be array-entered -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "PAL" wrote: I have a massive spreadsheet, one of the worksheets has several thousand rows, many columns. The sheet I am working on has about 200 rows. One of my columns is this: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2))} Works great when I reference H$1 or a "text value". When I move over a column: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2))} So, the only thing changed is =I$1. If I replace I$1 with "text" it works. I even tried retyping I1. I would rather reference since the text could change. Ideas. Is the spreadsheet simply too big? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
Still no good and have checked for extra spaces before and after the text in
I$1. "Max" wrote: From the sounds of it .. Try replacing this term in your expression: -- ('Raw Deviation'!$B$2:$B$6016=I$1) with this: -- (TRIM('Raw Deviation'!$B$2:$B$6016)=TRIM(I$1) ) P/s: Don't think your formula needs to be array-entered -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "PAL" wrote: I have a massive spreadsheet, one of the worksheets has several thousand rows, many columns. The sheet I am working on has about 200 rows. One of my columns is this: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!A$2:$A$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=H$1))/$E2))} Works great when I reference H$1 or a "text value". When I move over a column: {=IF(ISERROR(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2),"",(SUMPRODUCT(--('Raw Deviation'!$A$2:B$6016=$A2),--('Raw Deviation'!$D$2:$D$6016="Deviation"),--('Raw Deviation'!$B$2:$B$6016=I$1))/$E2))} So, the only thing changed is =I$1. If I replace I$1 with "text" it works. I even tried retyping I1. I would rather reference since the text could change. Ideas. Is the spreadsheet simply too big? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct
Well, thought that was a good shot. Maybe other responders would have other
ideas for you. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "PAL" wrote in message ... Still no good and have checked for extra spaces before and after the text in I$1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Sumproduct Help | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) |