brodskyfu You need to transpose first the range Q7:S7 then multiply and sum.=SUM(TRANSPOSE(Q7:S7)*R10#) SUMPRODUCT() will also work.=SUMPRODUCT(TOCOL(Q7:S7),R10#)

Why SUM doesn't work out with dynamic array?

8 Replies

PeterBartholomew1
Silver Contributor
Oct 09, 2024
brodskyfu
There is also an old mathematical function that performs precisely the calculation you specified.

MMULT is a very fast function but, despite that, I would more often use the other formula
= MMULT(rowArr, colArr#) = SUM(TOCOL(rowArr) * colArr#)
- brodskyfu
  Copper Contributor
  Oct 09, 2024
  Tks buddy！ Ring a bell in memory~ 🙂
Harun24HR
Bronze Contributor
Oct 09, 2024
brodskyfu You need to transpose first the range Q7:S7 then multiply and sum.
=SUM(TRANSPOSE(Q7:S7)*R10#)
SUMPRODUCT() will also work.
=SUMPRODUCT(TOCOL(Q7:S7),R10#)
- brodskyfu
  Copper Contributor
  Oct 09, 2024
  What is the difference in Excel mechanism behind?
  - SergeiBaklan
    Diamond Contributor
    Oct 09, 2024
    brodskyfu
- brodskyfu
  Copper Contributor
  Oct 09, 2024
  I checked the Transpose function, it just looks like that Transpose just rotates the range, but what is the reason behind the difference? I want understand it deeper. Tks~
brodskyfu
Copper Contributor
Oct 09, 2024
What's the simplest formula to sum it up? Tks~

Forum Discussion

Why SUM doesn't work out with dynamic array?

8 Replies

Resources