0

I am working on a sheet where the sumproduct equation is used. This formula is failing:

=SUMPRODUCT($D2:$K2, INDIRECT( ADDRESS(MATCH($B2,P_OIL,0) + ROW(P_OIL) -1, 4 ) &":"& ADDRESS(   MATCH( $B2, P_OIL, 0) + ROW(P_OIL) - 1, 11)))

I have tried using the address functions seperately to view their results.

=ADDRESS( MATCH($B2,P_OIL,0) + ROW(P_OIL) - 1, 4 )

Returns $D$43

=ADDRESS( MATCH($B2,P_OIL,0) + ROW(P_OIL) - 1, 11)

Returns $K$43

Therefore, SumProduct should be between the ranges $D2:$K2 and $D$43:$K$43; however, a "#VALUE" error is returned. I have tried changing the absolute referencing for the ranges but this has no effect. Is there a problem with defining the first range explicitly and the second through an INDIRECT function?

teepee
  • 2,620
  • 2
  • 22
  • 47
  • Thanks, this brought to my attention that the issue is that ROW returns an array; however, it doesn't seem to be remedied by returning the SUM (or in my case the MIN, to get the first row of that range) because it's a still an array value. For instance if my range is from A1:A3 then SUM(ROW(A1:A3)) will give 1 unless I input it as a matrix formula, in which case it will return 6. – teepee Oct 14 '14 at 22:26

1 Answers1

1

To return a whole row based on a match in a column of data, which is what you seem to be doing with the second part of that formula, it's normally better to use INDEX, e.g. If you want to match B2 in C3:C100 and return the corresponding row, columns D to K then use this setup:

INDEX(D3:K100,MATCH(B2,C3:C100,0),0)

Using zero as the column argument in INDEX gives you the whole row

You should be able to use that in SUMPRODUCT, adapting the ranges for the specifics of your setup.

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I normally use this method in other cases, but in this scenario the locations of the lookup ranges are listed one after the other, and so although I could use this method to find the location of the data within each specified range, I would still need to be able to define the row value where the range starts in order to find its absolute position. – teepee Oct 15 '14 at 15:33
  • Not sure I understand - specifically "the locations of the lookup ranges are listed one after the other" – barry houdini Oct 15 '14 at 15:41
  • It's sort of a convoluted layout. The key issue now though, is to find a way to return the first row of a given range as a value and not an array, which is what I'm looking for. SUM(ROW(Range1)) as I've seen suggested elsewhere, doesn't seem to work for me. – teepee Oct 15 '14 at 15:56
  • As you said above, you would surely need `MIN(ROW(P_OIL))` - that works for me – barry houdini Oct 15 '14 at 16:55
  • MIN(ROW(P_OIL)) gives the proper value in its own cell but I believe it is still an array formula. When I use it within indirect and sumproduct I still get #VALUE. – teepee Oct 15 '14 at 19:42
  • I set up a sample and used your exact `SUMPRODUCT` formula but using `MIN` around `ROW(P_OIL)`in there and I got numeric results - do you have any #VALUE! errors in any of the referenced ranges? – barry houdini Oct 15 '14 at 19:54
  • There are no problems like that in the cells, no. But MIN isn't doing the trick. I'll keep fiddling. Thanks. – teepee Oct 15 '14 at 20:25