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? |
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) |