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


--
Biff
Microsoft Excel MVP


"RagDyeR" wrote in message
...
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 !
-----------------------------------------------------------------------------------------------

"JB99" wrote in message
...
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" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JB99" wrote in message
...
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.