Problem with using INDIRECT with SUMPRODUCT and ROW()

[Guest]

I'm encountering a strange issue, I was hoping that some experts here might
be able to help.

If I enter the formula: =SUMPRODUCT(INDIRECT("A1:A2")), and put values in A1
and A2, then this formula will correctly sum them (since there is no 2nd
array, SUMPRODUCT simply sums the values in the 1st arry.

However, if I enter the following formula into cell A8 (or any other cell,
this is just to make an example):

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

Then I get a #VALUE! error.

There seems to be something about using SUMPRODUCT, INDIRECT, and ROW all
together that causes this, because if I replace SUMPRODUCT with a simple SUM,
i.e.:

=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

Then I get an answer!

Or if I simply try:

=SUMPRODUCT(INDIRECT("A1:A2"))

However I'd like to be able to base this formula on the current row, so need
to use all 3 together... Any ideas?????

Thanks.

 

[RagDyer]

What exactly are you trying to do?

Since you say that you need to reference the current row,
AND
=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
*DOES* work,

What *doesn't* work?

 

[Guest]

Thanks RagDyer,

Sorry if I wasn't clear:

Although
=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
DOES work,

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
returns #VALUE!

Seems strange to me!

 

[RagDyeR]

You still haven't stated any particular calculation you're trying to
complete.

If you're simply commenting ... yes ... I agree it's strange.

If you're looking for a reason or explanation ... sorry, I don't have one.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Thanks RagDyer,

Sorry if I wasn't clear:

Although
=SUM(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
DOES work,

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))
returns #VALUE!

Seems strange to me!

 

[T. Valko]

=SUMPRODUCT(INDIRECT("A" & ROW()-7 & ":A" & ROW()-6))

It's because ROW returns an array. Even thought the array is a single
element it's still an array. INDIRECT passes the *array* of references to
SUMPRODUCT which it can't handle. I've seen others refer to this as
dereferencing.

Normally you can get around this by using either the N() or T() functions.
N() for numeric values and T() for text values. However, in this case it
still doesn't work because for some reason N() is only recognizing the first
element of the references:

A1 = 10
A2 = 20

Formula entered in A8:

=SUMPRODUCT(N(INDIRECT("A"&ROW()-7&":A"&ROW()-6)))

Returns 10

So, we solved one problem and stumbled upon another!

I can't figure out why N() isn't passing the whole array. You'd think it
should since SUMPRODUCT works with arrays.

However, all is not lost! This works:

=SUMPRODUCT(INDEX(INDIRECT("A"&ROW()-7&":A"&ROW()-6),,1))

Returns 30.

But why use that when you can use this:

=SUM(INDIRECT("A"&ROW()-7&":A"&ROW()-6))

 

[Guest]

Excellent!!

Thanks so much, Biff - I was unaware that ROW() returned an array.

I actually called Microsoft support, paid them $49, and they are "looking
into it." After 3 hours, I still haven't heard anything.

I didn't just use SUM, because I actually want the sum of the squares of
each cell in the range - so I am using SUMPRODUCT and passing the whole
INDIRECT reference twice. I am using ROW() because this is a sliding window
of values, so each new row of calculation needs to use a new set of cells to
calculate the sum of the squares.

It's all working now - I really appreciate the help.

Jeff

 

[Peo Sjoblom]

I can tell you one thing, people get far better support by volunteers here
than by paying MS
They simply have no clues about the latest development of tricky formulas,
just look at how sparse help is about these things, look at the help for
SUMPRODUCT which is only about what it was first intended to do A1*B1+A2*B2
and so on. I would be surprised if you get a correct answer from them, in
fact it would be interesting to see what they say. If you get an answer
could you post it here?



--


Regards,


Peo Sjoblom

 

[Pete_UK]

And if the answer is not as good as Biff's, can you claim your money
back ?

Pete

 

[T. Valko]

I am using SUMPRODUCT and passing the whole

INDIRECT reference twice.

If you want the sum of squares:

=SUMPRODUCT(INDEX(INDIRECT("A"&ROW()-7&":A"&ROW()-6),,1)^2)

 

[T. Valko]

You bring up a good point, Peo.

Seems to me that MS *should* have support personnel that can solve/answer
these types of requests for support and it shouldn't take hours or days.

Are they hiring? I need a job!

 

[T. Valko]

Just to add...

I'm sure the OP's experience isn't uncommon.

The person answering the phone at MS doesn't necessarily need to know the
answer but they should take the information and be able to route the call to
someone that knows the answer.

 

[RagDyeR]

Are you joking about that "need a job"?

Is retirement "getting to you"?

I'm seriously getting to (thinking about) that point.

I'm afraid that I *need* a place to go to every morning!

Hear too many "bad" things about acquaintances who have gone that route.


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

You bring up a good point, Peo.

Seems to me that MS *should* have support personnel that can solve/answer
these types of requests for support and it shouldn't take hours or days.

Are they hiring? I need a job!