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?

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~ 🙂
brodskyfu
Copper Contributor
Oct 09, 2024
What's the simplest formula to sum it up? Tks~
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
  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 is the difference in Excel mechanism behind?
  - SergeiBaklan
    MVP
    Oct 09, 2024
    brodskyfu

Forum Discussion

Why SUM doesn't work out with dynamic array?

Share

Resources