SUMPRODUCT inside ArrayFormula

Question

I have a fixed vector, say $A$1:$E$1

I also have a vector per row, say A2:E2, A3:E3, A4:E4...

Now I want output in Column F as

F2 = sumproduct(A1:E1,A2:E2)
F3 = sumproduct(A1:E1,A3:E3)
F4 = sumproduct(A1:E1,A4:E4)
...

Now I want to put these into one array formula, but I don't know how to do it. For example

F2 = Arrayformula(sumproduct($A$1:$E$1,A2:A:E2:E))

Meaning multiply A1-E1 vector with A2:E2, then A3:E3 and so on.

How do I do this?

user0 · Accepted Answer · 2019-05-23 13:24:11Z

0

=ARRAYFORMULA(IF(LEN(A2:A&B2:B&C2:C&D2:D&E2:E), 
 MMULT(A1:E1*A2:E,TRANSPOSE(COLUMN(A1:E1)^0)), ))

answered May 23, 2019 at 13:24

user0

26k8 gold badges49 silver badges77 bronze badges

Thank you. I'm having a bit trouble parsing the formula but seems to work.
– Paul
May 24, 2019 at 14:59
I have played around with this "=A2:A&B2:B&C2:C" and I have no idea what it does. It just gets some of the cells as a string but for a life of me it seems so random what cells it gets. Can you clarify? And then Length of that, ok. But then IF that length? What does that do?
– Paul
May 24, 2019 at 15:09
COLUMN(A1:E1) just returns a single digit of the column, that to ^0 is then just always "1" isn't it?
– Paul
May 24, 2019 at 15:17
can you share a copy of your sheet?
– user0
May 24, 2019 at 16:24
link The cell I wanted to put this into is idx!AB12 The fixed vector is idx!P8:AA8 and the row of vectors is idx!D12:D ... O12:O
– Paul
May 24, 2019 at 21:01

| Show 3 more comments

1 Answer 1