How to Use the Excel MMULT Function for Matrix Operations

Dive deep into the world of Excel with our comprehensive tutorial on matrix multiplication and array formulas! This video is a continuation of a previous lecture, focusing on a practical application of these advanced Excel techniques.

In this video I introduce Excel's Matrix Multiplication formula - the MMULT function. In simple steps I explain how you can use MMULT to solve complex problems.

🔑 Key Highlights:

Matrix Multiplication Explained: Grasp the fundamentals of matrix multiplication and how to apply it in Excel.
Step-by-Step Guide: Follow a detailed walkthrough from setting up matrices to executing complex array formulas.
Comparing Techniques: Learn the differences and applications of SUMPRODUCT, MMULT, and other array functions in real-world scenarios.
Dynamic Formula Creation: Discover how to make your formulas dynamic and adaptable to changes in data.

In this example I use the MMULT formula to get the number of rows (workshops in this case) that contain at least one of the criteria (companies in this case). The analysis involves multiple criteria with an OR condition.

I first explain the basics and the rules you need to follow to use the MMULT formula correctly. If you don't, you formula result will be #value. I then use the SUMPRODUCT, together with MMULT to get unique count by rows. To make it more dynamic, I use the COLUMN function together with TRANSPOSE.

In the last video, I showed you how you can use helper cells first and then how to combine SUMPRODUCT with FREQUENCY function to get a one formula array solution.

🚩Let’s connect on social:

#excel

[wayneedmondson]

Hi Leila.. this is an excellent 3-part series for anyone wanting to understand array formulas and matrix manipulations. I learned a lot. Thanks for Part 2 of 3. Double thumbs up!!

[excelisfun]

Great to have you as part our our amazing Excel Online Team!!!

[makisalimhussain]

Thanks Leila, it is such a great tutorial especially the MMULT function part.

But, I prefer to obtain the first array in different way which I believe it is shorter especially if the conditions are many but not only 3.

Instead of writing which is equal to
{1, 1, 0;1, 0, 1;0, 0, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 0, 0;0, 1, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 0, 0;0, 1, 0;0, 1, 0;0, 1, 0;0, 0, 0;1, 0, 0;1, 0, 1;0, 0, 1;1, 0, 1;0, 1, 0;0, 1, 1;0, 1, 1;0, 0, 0;0, 0, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 1, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 0;1, 0, 1;1, 1, 0;0, 0, 0;0, 0, 0;0, 0, 0;1, 1, 0;0, 0, 0;0, 0, 0;1, 0, 0;0, 0, 1;1, 0, 1;0, 1, 0;0, 0, 0;0, 0, 0;0, 1, 0}

I prefer to obtain the same result by using COUNTIFS function which is COUNTIFS(P3:P5, B4:D53) resulting

{1, 1, 0;1, 0, 1;0, 0, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 0, 0;0, 1, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 0, 0;0, 1, 0;0, 1, 0;0, 1, 0;0, 0, 0;1, 0, 0;1, 0, 1;0, 0, 1;1, 0, 1;0, 1, 0;0, 1, 1;0, 1, 1;0, 0, 0;0, 0, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 1;0, 1, 0;0, 0, 0;0, 0, 0;0, 1, 0;0, 0, 0;1, 0, 1;1, 1, 0;0, 0, 0;0, 0, 0;0, 0, 0;1, 1, 0;0, 0, 0;0, 0, 0;1, 0, 0;0, 0, 1;1, 0, 1;0, 1, 0;0, 0, 0;0, 0, 0;0, 1, 0}

Maki S. Hussain
Iraq

[RajendraPatel.]

Nice tips ever I learned in my Excel life. Thanks

[kangsungho]

I don't like Sumproduct to have CtrlShiftEnter, either.
Great to know you have a common thing with me. :)

[farzanmoha]

Hi Leila,
Happy nowrooz
How could I find the matches in different rows that should have more than one match cell to be counted as a match and put their assigned ”for example assigned actual” in one row?

[doperich]

how to form series of 1, 2, 3 in column without copy paste?.like cell a1-1, a2-2, a3-3 again a4-1, a5-2,a6-3