Mixing INDIRECT & ADDRESS with SUMPRODUCT - Confusion with Strings

Hello all

My problem is I need to use the SumProduct function on variable length arrays. Hence I believe I need to use the Indirect function together with a string address as the parameters of the SumProduct function.

I am assembling the array address string using the Address function and given it is an array, I'm concatenating the address as follows (I've replaced various formulas etc with hard-coded numbers for simplicity):

Please Login or Register  to view this content.

Straightforward so far.

Testing on its own, it produces the string "$B$3:$B$7" as expected.

The problem is when I apply it to the INDIRECT formula as follows

Please Login or Register  to view this content.

I get a #VALUE! error

But when I replace the COLUMN(B3) formula's with hard coded numbers

Please Login or Register  to view this content.

all is good.

A clue might be that when using the in-cell debugging feature of Excel, the hard-coded Indirect function evaluates to a 5 element array {1;2;3;4;5} while the first Indirect function evaluates to a single element array {1}. But why?


I'm new to this forum and in fact have not been on any forums for many years. Hence apologises if I violate any protocols - constructive criticism very welcome.

I have a guru colleague working on it. If she finds a solution, I'll post.

I found a couple of alternative solutions that got around this problem. The first involved breaking the Address concatenation calculation out into separate cells and the other involved restructuring the data and using the SUMIF function. Both involved a lot of extra columns for intermediate calculations.

There are usually alternative solutions to most problems but I would like to understand the core issue in the problem I posed above. I'm sure there is revealing insight to be had.

Originally Posted by benishiryo

hi apembo, welcome to the forum. like you, i would also love to know what went wrong with my solution rather than getting an alternative. it seems that SUMPRODUCT doesn't go very well with INDIRECT & COLUMN. that's just my guess of course. but since you mentioned about your alternatives needing separate & extra columns, here's one that don't:
=SUMPRODUCT(OFFSET(B$3,,,5),Q3:Q7)

Thanks benishiryo. That's actually perfect - appreciated. Much more elegant than what I was attempting.

I believed I've uploaded a canned example illustrating my issue and with your solution to assist others.

Thats the second time I've used the OFFSET function - I will have to keep it mind.

Again excellent - thanks all.

Originally Posted by daddylonglegs

It's COLUMN function that causes the problem - as you discovered it returns an "array" like {2} rather than a number 2 - ...

Aaaaahh - thats the eureka moment I was hoping for.

I just went and applied the column function as follows:

Please Login or Register  to view this content.

and it returns 3, but when you delve in, it's actually returned a 4 element horizontal array {3,4,5,6}

Brilliant. Thankyou

PS: arrays are one area of Excel I have largely ignored. My curiosity is aroused ...