0

Is there a way to use SUMPRODUCT or use an array formula with arrays of differing sizes?

Currently I have a list of ~1500 products in column A and columns B-I contain their respective component needs like so:

 A        B      C      D      E      F      G      H      I      J
        ¢20   ¢55     ¢75    ¢61    ¢12    ¢30    ¢46    ¢83     total
wdgt     9      1      1      0      0      0      0      0        235
fzzy     0      0      10     9      3      1      1      0       1411
sqr      10     10     10     10     10     10     10     10      3820
llc      2      4      6      8      10     12     14     16      3650
dbl      2      4      8      16     32     64     128    256    31276
....

Each of the respective components has a set value , and J is currently calculated by

SUMPRODUCT(B2:I2,$B1:$I1)...

I would like to have J be an array formula like

{=SUMPRODUCT(B2:I6,$B1:$I1)}

but doing so gives #VALUE! error and:

{=SUM(B2:I6,$B1:$I1)}

for each row i.e. the sum of the total column, 40392.

Is there a way to do this with an array formula?

  • 1
    Please define "strange results" and "strange behavior". We don't know what that is. – CharlieRB Aug 25 '14 at 13:25
  • 1
    Also, your formula is multiplying by the text (prt1, prt2, prt3, etc.) in the first row. That won't work. Was that your intent? – CharlieRB Aug 25 '14 at 13:34
  • For that matter, please define "correct behavior"; i.e., what do you want it to do? Giving some examples of the results you want/expect would help us to help you get them. Also, why are you making the columns B and I absolute, but not the row numbers? And, just for good measure, what version of Excel are you using? – G-Man Aug 25 '14 at 22:55
  • Why would you want to SUM if you want an array? – LDC3 Aug 26 '14 at 0:51
1

Via trial and error

{=SUM(B2:I6,$B1:$I1)}

returns an error #VALUE!

{=SUM(B2:I6,$B1:$I1)}

returns the sum of the totals

{=$B$1:$I$1*B2:I6}

returns 180, 0, 200, 40, 40 (which I cannot explain). This was what I thought was strange.

{=$B$1*B2:B6+$C$1*C2:C6+…+$H$1*H2:H6+$I$1*I2:I6}

returns the correct totals for each row.

Alternatively, I previously had something like:

=SUMPRODUCT($B$1:$I$1,B2:I2)

in each row, but for 1500+ rows, the evaluation was getting longer and longer as the list is constantly changing and increasing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.