2

I have checked all question related to this topic, but none of it helped.

I have this formula

=SUMPRODUCT((INDIRECT("Ap"&ROW()&":"&"Ap"&(ROW()+$T4-1)));(INDIRECT("Ap"&ROW()&":"&"Ap"&(ROW()+$T4-1))))

This results in - 0.

=SUMPRODUCT((INDIRECT("Ap"&ROW()&":"&"Ap"&(ROW()+$T4-1)))*(INDIRECT("Ap"&ROW()&":"&"Ap"&(ROW()+$T4-1))))

This results in - "VALUE!

With SUM command everything worked fine, but I'm stuck with this now. I use Microsoft Office 2013 and I can't seem to find out the problems with this INDIRECT function, since it works nicely with other command.

For example, if I have in column A numbers from 1 to 5 in the first 5 rows, and the same in column B, I put the formula in C1

=SUMPRODUCT((INDIRECT("A"&ROW()&":"&"A"&(ROW()+4)))*(INDIRECT("B"&ROW()&":"&"B"&(ROW()+4))))

Results the same as the original example.

shA.t
  • 16,580
  • 5
  • 54
  • 111
energyMax
  • 419
  • 1
  • 8
  • 16
  • What is the formula trying to achieve? i.e. what does the data look like and what do you want the result of the formula to be? – tospig Apr 18 '15 at 11:42
  • The INDIRECT gets data from two columns, where only numbers are placed. So it should multiply the numbers and the end result should be... a single number of course – energyMax Apr 18 '15 at 11:48
  • If SUM works but SUMPRODUCT throws a #VALUE! error, then look for text that looks like numbers. SUM will happily skip over these but SUMPRODUCT will choke and spit up a #VALUE! on any text. –  Apr 18 '15 at 12:02
  • @Jeeped Thanks, but it's the same. I tried much simplified case and the problem remains. I put the example above – energyMax Apr 18 '15 at 12:08

2 Answers2

3

The construction you are using is quite poor, to be honest. Not only is the unqualified ROW() a very unrigorous choice, but volatile INDIRECT constructions can almost always be avoided in this type of set-up.

Much better is:

=SUMPRODUCT(INDEX(A:A,ROWS($1:1)):INDEX(A:A,ROWS($1:1)+4),INDEX(B:B,ROWS($1:1)):INDEX(B:B,ROWS($1:1)+4))

See here for a discussion on the advantages of using ROWS instead of ROW:

http://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15
  • Big plus one. Vast improvement over the original model. I see a lot of INDIRECT and OFFSET used in the Refers to: of a dynamic named range when INDEX would be so much more appropriate. –  Apr 18 '15 at 12:34
  • @Jeeped Thanks. And I couldn't agree more. It's unfortunate that, for whatever reason, constructions involving OFFSET, INDIRECT (and even ADDRESS) abound - and are even recommended by some sources - around the internet, especially when, as you suggest, they can almost always be replaced with a simple, non-volatile INDEX set-up. – XOR LX Apr 18 '15 at 12:38
  • 1
    @XORLX Thanks for clarifications. But the advantage of using ROW in a big data sheet is that it gives you the location of the formula (e.g. row number), wheras ROWS doesn't do that. It does enable you to replace INDIRECT, but i can't see it as a replacement for ROW() – energyMax Apr 18 '15 at 16:21
  • @Gasper I don't understand your comment, I'm afraid. How does ROW "give you the location of the formula"? – XOR LX Apr 18 '15 at 16:39
  • 1
    @XORLX Location is perhaps a clumsy expression. ROW() gives you the row number, where the exact cell is (eg. ROW() in E11 is 11, which you most surely know). This can be a very helpful way to address certain formula, if you need to dynamically change ranges – energyMax Apr 18 '15 at 17:29
  • But the same CAN be achieved using ROWS, and you don't suffer from the drawbacks inherent in ROW (which I explain in the link I posted). – XOR LX Apr 18 '15 at 19:57
1

Using your example with data in columns A and B, and this answer on a similar question, the ROW() function returns an array, rather than a single value. Wrapping it in sum() solves this.

=SUMPRODUCT(INDIRECT("A"&SUM(ROW())&":"&"A"&SUM((ROW()+4))),INDIRECT("B"&SUM(ROW())&":"&"B"&SUM((ROW()+4))))
Community
  • 1
  • 1
tospig
  • 7,762
  • 14
  • 40
  • 79