SumProduct array problem

[didijaba]

HI, I have this formula and it keeps giving me 0. I do not know what I did wrong, read posts but haven't found solution.

=SUMPRODUCT(OFFSET($D$88,0,0,1,COUNTA(D88:AA88)-1),OFFSET($D$84,0,0,1,COUNTA(D84:AA84)-1),OFFSET($D$83,0,0,1,COUNTA(D83:AA83)-1))

 

[pgc01]

Hi

If you try it directly with the ranges without the Offset(), like

=SUMPRODUCT($D$88:$Z$88,$D$84:$Z$84,$D$83:$Z$83)

do you get the result you expect?

 

[Aladin Akyurek]

Do all of these ranges house numeric values?

 

[didijaba]

yes, all are numeric.

 

[didijaba]

ADVERTISEMENT

Range is dynamic.

 

[didijaba]

HI, if I place ranges without offset, I get proper number out. Thing is ranges are dynamic, so I need to define range using offset or something like that. And on right side of range I have total column, so I can't just cheat and make wider range then used.

 

[didijaba]

ADVERTISEMENT

This what works, if you have something better please advise.

=SUMPRODUCT(N(INDIRECT("$D$88:"&ADDRESS(88,COLUMNS(INDIRECT("A88:"&(CELL("address"))))))),N(INDIRECT("$D$83:"&ADDRESS(83,COLUMNS(INDIRECT("A83:"&(CELL("address"))))))))

 

[didijaba]

Sorry, this isn't ok. I just noticed. This is correct

=SUMPRODUCT((INDIRECT("$D$92:"&ADDRESS(92,COLUMNS(INDIRECT("A92:"&(CELL("address"))))-1))),(INDIRECT("$D$36:"&ADDRESS(36,COLUMNS(INDIRECT("A36:"&(CELL("address"))))-1))))

 

[Aladin Akyurek]

yes, all are numeric.

In A1 (or any cell that I convenient) enter:

=MATCH(9.99999999999999E+307,D88:AA88)

Now invoke:

=SUMPRODUCT(D88:INDEX(D88:AA88,A1),$D$84,INDEX(D84:AA84,A1),$D$83:INDEX(D83:AA83,A1))

 

[didijaba]

Thanks @Aladin Akyurek