SUMPRODUCT+INDIRECT error

[aberrator2000]

Hi,
I'm pretty new to Excel and just got stuck on a problem.
I have a formula that gets a part number from a cell and then adds some values from multiple records with the same part number on an another sheet. Works perfectly if I use a direct reference:
=SUMPRODUCT((StykerWIP!A$2:A$563=$B$3)*(StykerWIP!C$2:C$563))
__________________________________^^^
But if I try to use INDIRECT I get a #VALUE error:
=SUMPRODUCT((StykerWIP!A$2:A$563=INDIRECT("B"&(ROW()))*(StykerWIP!C$2:C$563)))

The part number is text - "6939-1-430" - and when I use INDIRECT separately it returns the value just fine.
=INDIRECT("B"&(ROW()))

What am I doing wrong?

TIA,
Bart

[Jazzer]

Hi,

The reason for the error is that ROW() returns an array and it cannot be connected with a string ("B"). I think it is so because it's inside the SUMPRODUCT function. Take the first part of the array with INDEX() function and it should work. Like this:

Please Login or Register  to view this content.

- Asser

[aberrator2000]

Doesn't work, still the same error...

[arthurbr]

Doesn't work, still the same error...

In which cell do you get an error?
Why use the INDIRECT function?
If you want to pull the formula down to adapt, juste use =$b3

[aberrator2000]

I had some problems with bad values when I sorted the data using relative references, but they seem to be gone now, must've been something else. Works perfecly now.
Sheesh.

Still I'm curious why indirect doesn't work...
I get errors in all the cells in the L-K range.

[EdMac]

Is the other workbook closed? Indirect only works on open workbooks.

[NBVC]

I had some problems with bad values when I sorted the data using relative references, but they seem to be gone now, must've been something else. Works perfecly now.
Sheesh.

Still I'm curious why indirect doesn't work...
I get errors in all the cells in the L-K range.

Indirect() is used when your are indirectly referencing a cell...not directly referencing a cell..

Read Excel help files on INDIRECT function.... there are some examples there that might help you to understand how it works.

[aberrator2000]

Both sheets are in the same workbook.

And reading help didn't really help (hmm) because according to it indirect simply returns the cell value, so when comparing it to another value it should work, as it does with SUM or anything else.

[NBVC]

The item in the cell you indirectly refer to must be a reference to another place...via a cell reference, named range/reference or sheetname..

e.g. in B2 you would have to have something like "Sheet2!B2"

[arthurbr]

As NBVC explained, INIDRECT references another cell.
If in a1 you have " hello", in a2 you have "a1", INDIRECT(A2)="hello", not a1

[aberrator2000]

That's exactly what I'm talking about!
A1 has the value of "6940-1-480"
when I put '=INDIRECT("A1")' into B1 the result IS 6940-1-480
Same when I put =INDIRECT("A"&ROW())

So it doesn't really have to be a reference, can be just a value too, and thus sumproduct should work, just as sum and other functions work.
I just don't get it.

[JakeJAMR]

Hi,

The reason for the error is that ROW() returns an array and it cannot be connected with a string ("B"). I think it is so because it's inside the SUMPRODUCT function. Take the first part of the array with INDEX() function and it should work. Like this:

Please Login or Register  to view this content.

- Asser

This solved the problem I was having... thanks Asser!