Frustrating SUMPRODUCT problem.

[rmellison]

This problem is doubly frustrating because I had it nailed down yesterday,
then my PC crashed and I lost my formula. And now I can't reproduce it....

I am trying to sum a number of items in a given row on another worksheet
(sheet 2), depending on whether the value in the header row in the
corresponding column falls between two values (call them A and B) in the
header row of my source worksheet (sheet 1). Let me demonstrate by example:

Sheet 1 Sheet 2
60 65 70 75 61 63 64 67 71 72
1 X 1 2 4 5 3 2
6
2 Y 2 1 0 4 2 1
3
3 Z 3 2 4 6 5 2
3

For X, the two values (A and B) between which sheet 2 header must fall
batween are 60 and 65. So I would expect X to be 2+4+5 = 11
For Y, A and B are 65 and 70, and I need to look at row 2, so Y = 2
Similarly Z should be 2+3 = 5

Now for an extra twist, I am selected different sheets using a combo box,
and linking that to a cell which is referred to by my formula so that I can
switch sheet 2 and it will update automatically.

Here's what I have, and I think its close to what I had before, but I'm
getting a #VALUE error message. It is array entered. The INDEX( ) function
refers to my list of sheets and my combo box selection.

=SUMPRODUCT(--((INDIRECT((INDEX($A$9:$A$72,$A$6))&"!B1:CW1"))>D$1),--((INDIRECT((INDEX($A$9:$A$72,$A$6))&"!B1:CW1"))<E$1),INDIRECT((ADDRESS(ROW(),COLUMN(B2),,,INDEX($A$9:$A$72,$A$6)))&":"&(ADDRESS(ROW(),COLUMN(CW2)))))

It looks messy, and I'm sure it could be tidied up, but (i think) I need to
use ADDRESS( ) because I have to be able to drag the cells down and across
and update the rows/columns accordingly, and using INDIRECT with cell refs as
strings does not update them when dragged.

Please can someone put me out of my misery, or suggest an altogether much
better way of achieving the same result.

Thanks in advance.

[rmellison]

I have narrowed down the problem to the third array in the SUMPRODUCT
function. If I use
INDIRECT(INDEX($A$9:$A$72,$A$6)&"!B2:CW2")
instead of
INDIRECT((ADDRESS(ROW(),COLUMN(B2),,,INDEX($A$9:$A$72,$A$6)))&":"&(ADDRESS(ROW(),COLUMN(CW2))))
I get what I want. But I need to be able to drag the thing down and across
with relative references, otherwise I'll be changing 80,000 formulae manually.

This is driving me bonkers.


"rmellison" wrote:

> This problem is doubly frustrating because I had it nailed down yesterday,
> then my PC crashed and I lost my formula. And now I can't reproduce it....
>
> I am trying to sum a number of items in a given row on another worksheet
> (sheet 2), depending on whether the value in the header row in the
> corresponding column falls between two values (call them A and B) in the
> header row of my source worksheet (sheet 1). Let me demonstrate by example:
>
> Sheet 1 Sheet 2
> 60 65 70 75 61 63 64 67 71 72
> 1 X 1 2 4 5 3 2
> 6
> 2 Y 2 1 0 4 2 1
> 3
> 3 Z 3 2 4 6 5 2
> 3
>
> For X, the two values (A and B) between which sheet 2 header must fall
> batween are 60 and 65. So I would expect X to be 2+4+5 = 11
> For Y, A and B are 65 and 70, and I need to look at row 2, so Y = 2
> Similarly Z should be 2+3 = 5
>
> Now for an extra twist, I am selected different sheets using a combo box,
> and linking that to a cell which is referred to by my formula so that I can
> switch sheet 2 and it will update automatically.
>
> Here's what I have, and I think its close to what I had before, but I'm
> getting a #VALUE error message. It is array entered. The INDEX( ) function
> refers to my list of sheets and my combo box selection.
>
> =SUMPRODUCT(--((INDIRECT((INDEX($A$9:$A$72,$A$6))&"!B1:CW1"))>D$1),--((INDIRECT((INDEX($A$9:$A$72,$A$6))&"!B1:CW1"))<E$1),INDIRECT((ADDRESS(ROW(),COLUMN(B2),,,INDEX($A$9:$A$72,$A$6)))&":"&(ADDRESS(ROW(),COLUMN(CW2)))))
>
> It looks messy, and I'm sure it could be tidied up, but (i think) I need to
> use ADDRESS( ) because I have to be able to drag the cells down and across
> and update the rows/columns accordingly, and using INDIRECT with cell refs as
> strings does not update them when dragged.
>
> Please can someone put me out of my misery, or suggest an altogether much
> better way of achieving the same result.
>
> Thanks in advance.
>
>