MMULT Usage
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
Still loading...
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBH
1
2
3
4
This guide has been remade in the newest version of Sheets. There are subtle differences in the behaviour of MMULT (and other functions) in the newest version, so it is recommended that you view (and copy, if you wish) this updated spreadsheet:
5
https://docs.google.com/spreadsheets/d/1NJPAt5iFQWIyxHx35bpCh6zjVeKon4rdTPk-dfyZDrU/edit?usp=sharing
6
7
8
9
10
11
12
13
14
Matrix multiplication involves multiplying and summing elements of two matrices to produce a result matrix as such:
15
16
17
abef=ae + bgaf + bhMatrix multiplication on Wikipedia
18
cdghce + dgcf + dh
19
20
21
The result matrix has the same number of rows as the first matrix being multiplied, and the same number of columns as the second matrix being multiplied. Furthermore, the second matrix must have the same number of rows as the first matrix has columns.
22
23
So the following would be another example of a valid matrix multiplication, where a 3 x 2 matrix is multiplied by a 2 x 1 matrix:
24
25
26
abg=ag + bh
27
cd*hcg + dh
28
29
efeg + fh
30
31
32
33
Taking the above example, suppose the second matrix being multiplied consists entirely of 1's. This effectively simplifies the matrix multiplication to returning a sum of each row of the first matrix:
34
35
36
ab1=a*1 + b*1=a + b
37
cd*1c*1 + d*1c + d
38
39
efe*1 + f*1e + f
40
41
42
43
The "column of 1's" method is frequently used in Google Spreadsheets to build a MMULT array formula that performs calculations on rows down an entire column, which is especially useful when new rows are constantly being generated (for example, with Form submissions).
44
45
Say you wanted to build a formula that would sum each row in the array A1:C4. This can easily be done with, say:
46
=ArrayFormula(A1:A4+B1:B4+C1:C4)
47
48
However this becomes arduous if you are summing across numerous columns. Another way to do it is by performing matrix multiplication with the MMULT function:
49
=ArrayFormula(MMULT(A1:C4;TRANSPOSE(SIGN(COLUMN(A1:C1)))))
50
51
Breaking each part down, we can represent the A1:C4 part as:
52
53
54
A1B1C1
55
A2B2C2
56
A3B3C3
57
A4B4C4
58
59
60
The COLUMN(A1:C1) part is a 1x3 array which simplifies to {1,2,3}:
61
62
63
COLUMN(A1)COLUMN(B1)COLUMN(C1)=123
64
65
66
SIGN( ) will return 1 for any positive value, -1 for any negative value, and 0 for a zero value. So SIGN(COLUMN(A1:C1)) simplifies to {1,1,1}:
67
68
69
SIGN(1)SIGN(2)SIGN(3)=111
70
71
72
And TRANSPOSE( ) will simply transpose the array from 1x3 to 3x1, thereby creating our desired column of 1's, having the same number of rows as our first array (A1:C4) has columns. So TRANSPOSE(SIGN(COLUMN(A1:C1))) becomes:
73
74
75
1
76
1
77
1
78
79
80
Now when we apply the matrix multiplication of A1:C4 with the result of TRANSPOSE(SIGN(COLUMN(A1:C3))), we are left with an array that is the sum of each row in A1:C4:
81
82
83
A1B1C11A1*1+B1*1+C1*1A1+B1+C1
84
A2B2C2*1=A2*1+B2*1+C2*1=A2+B2+C2
85
A3B3C31A3*1+B3*3+C3*1A3+B3+C3
86
87
A4B4C4A4*1+B4*1+C4*1A4+B4+C4
88
89
90
Loading...
 
 
 
Basic Overview
Sum across rows
Conditional sum across rows
Cumulative sum down column