Sum of a set of cells multiplied with substitute returns 0 or #VALUE

[BrentSmits]

Hello

I would like to know where my logic is faulty since Excel returns or 0 or #VALUE as a result.
I would like to transfer the following in a formula using INDIRECT and ADRES:
=SUM(T9:Y9*T6:Y6)

Some additional details:
The column letter can be created using indirect itself (row number, column number) or can be created using substitute(adres(...)). I tried both, but that didn't give me the result I wanted.
The problem seems to appear with the * element used for multiplying the rows.
Multiplying 2 sums using indirect works perfectly:
SUM(INDIRECT(SUBSTITUTE(ADDRESS(1;COLUMN(T9);4);1;"")&ROW(T9)&":"&SUBSTITUTE(ADDRESS(1;COLUMN(T9)+$S9-1;4);1;"")&ROW(T9)))*SUM(INDIRECT(SUBSTITUTE(ADDRESS(1;COLUMN(T$6);4);1;"")&ROW(T$6)&":"&SUBSTITUTE(ADDRESS(1;COLUMN(T$6)+$S9-1;4);1;"")&ROW(T$6)))
This is the same as: SUM(T9:Y9)*SUM(T6:Y6) and I can easely change the Y to X or U or V or ... depending on my needs.
Why do I need to use this?
I have calculated a value that tells me how many datapoints I need to use. If I need 6 datapoints (all of them), I could just use the array without INDIRECT but sometimes I need 5 datapoints or 4 or 3 or ... so I would like to calculate the cell reference (T9:Y9) is 6 cells for example.

It is really just this annoying * that doesn't do multiplying.
The following returns 0 (not using indirect) if you would like to test (just plug in values at cells T6:Y6 and then plug in numbers at cells T9:Y9 or any other line but this last numbers need to change all the time so you have to use $ at T6:Y6 if you use this in the formula):

=SUM(SUBSTITUTE(ADDRESS(1;COLUMN(T9);4);1;"")&ROW(T9)&":"&SUBSTITUTE(ADDRESS(1;COLUMN(T9)+$S9-1;4);1;"")&ROW(T9)&"*"&SUBSTITUTE(ADDRESS(1;COLUMN(T$6);4);1;"")&ROW(T$6)&":"&SUBSTITUTE(ADDRESS(1;COLUMN(T$6)+$S9-1;4);1;"")&ROW(T$6))

Using indirect gives me #VALUE
Even =SUM(INDIRECT(T9:Y9*T6:Y6)) gives me error (ctrl shift enter)

*The solution to this is ofcourse to use a SUMPRODUCT formula but I'm interested why this doesn't work as I see it. I want to know if I can improve my logic or learn something fundamental based on what I am doing so if you can make it work without SUMPRODUCT, please don't hesitate to do so, if it can't be solved, please explain what happens within my formula or why it can't work*

Thanks in advance!!!

 

[BrentSmits]

Edit:

I think that sumproduct doesn't work either since I get a result of 0 as well.
However, if I calculate the cell references in 2 different cells and then use sumproduct(indirect(Cell1);indirect(Cell2)) I get a number (15) that is correct and not 0.
If I then change the arguments (Cell1 and Cell2) using substitute and calculations to create the T9:Y9 and T6:Y6 references, I get 0 again?
Quite confusing for me so if anyone can help me out, I'd appreciate it a lot!

Thanks in advance!!!

 

[Eric W]

Welcome to the forum.

I think you have several misconceptions about how INDIRECT and ADDRESS work. You should only use INDIRECT to create a range from a string. If you have something like

=SUM(T6:Y6*T9:Y9)

You need to put an INDIRECT around each range, like:

=SUM(INDIRECT("T6:Y6")*INDIRECT("T9:Y9"))

Notice how the * is NOT inside either INDIRECT? Also, you can't use ADDRESS without INDIRECT. ADDRESS creates a string value, which you need to convert to a range before you can give it to a function which requires a range.


However, you might be able to do something simpler using OFFSET. Consider this:

=SUMPRODUCT(OFFSET(T6;0;0;;S9);OFFSET(T9;0;0;;S9))

If you put the value of 6 in S9, this will be equivalent of your original formula. If you change the value of S9 to 4, then it will multiply T6:W6 by T9:W6 and sum them. If you read up on OFFSET, the first parameter is the starting point, the next 2 parameters are the row and column offsets from that point, and the last parameter is how many columns to include in the range.

Hope this helps!

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...n-using-indirect-directly-in-the-formula.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[BrentSmits]

Hi both of you
First for Eric W:

The SUM formula with double indirect does work if I make it an array. However, I'm worried about the "" because I need to be able to make the formula "dynamic". I guess OFFSET can do this, never tried this formula but have seen it before. I'll give this a try.

For Fluff:

My sincerest apologies. I actually made an account on this forum and one on the excelforum, assuming they were seperate forums. I kinda explained the same (in less detail) on the other forum since I was more aware of what I wanted so I just skipped all the mumbo-jumbo I wrote here.
I posted a sample on that forum but I guess that the OFFSET will eventually do what I need if I figure out how to use it efficiently

Thanks for the help and I'll let you know if I can get it to work.

Kind regards

 

[BrentSmits]

Eric W:
I used OFFSET succesfully Used other arguments than what you provided me but I'm glad that you pointed out how easy this is once you get the hang of it.
This thread can be closed!

Thanks to everyone who helped ^^

 

[Eric W]

Happy we could help!