# SumProduct array problem

#### didijaba

##### Well-known Member
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.

Code:
``=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))``

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### pgc01

##### MrExcel MVP
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?

##### MrExcel MVP
Do all of these ranges house numeric values?

#### didijaba

##### Well-known Member
yes, all are numeric.

#### didijaba

##### Well-known Member
Range is dynamic.

#### didijaba

##### Well-known Member
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

##### Well-known Member
Code:
``=SUMPRODUCT(N(INDIRECT("\$D\$88:"&ADDRESS(88,COLUMNS(INDIRECT("A88:"&(CELL("address"))))))),N(INDIRECT("\$D\$83:"&ADDRESS(83,COLUMNS(INDIRECT("A83:"&(CELL("address"))))))))``

#### didijaba

##### Well-known Member
Sorry, this isn't ok. I just noticed. This is correct
Code:
``=SUMPRODUCT((INDIRECT("\$D\$92:"&ADDRESS(92,COLUMNS(INDIRECT("A92:"&(CELL("address"))))-1))),(INDIRECT("\$D\$36:"&ADDRESS(36,COLUMNS(INDIRECT("A36:"&(CELL("address"))))-1))))``

Last edited:

##### MrExcel MVP
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))

Replies
6
Views
474
Replies
3
Views
561
Replies
0
Views
2K
Replies
9
Views
514
Replies
9
Views
1K

1,195,683
Messages
6,011,138
Members
441,587
Latest member
kbsgiri09

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back