You’ll learn all about the Amazing MMULT Function in this Lecture!
In Part 2 we will create an Excel Array Formula using SUMPRODUCT and MMULT.
In this lecture I will show you how you can use Excel’s Matrix Multiplication formula, MMULT function to solve complex problems. I will use the same example as the last post to get unique count across rows. The tricky part here is: We nee to get unique count across rows AND we have multiple criteria.
Don’t forget to watch the video for PART 1 before looking at this solution.
In the video I explain the basics of the MMULT formula. There is one little rule you need to have in mind when you work with Matrix Multiplication.
MMULT however on it’s own is not enough to give us the answer. We need to use it together with SUMPRODUCT and later with TRANSPOSE and COLUMN functions to get a fully dynamic formula.
[…] on to PART 2 to learn about the MMULT […]
Hi Leila
thanks a lot we appreciate your great effort
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 (B4:D53=P3)+(B4:D53=P4)+(B4:D53=P5) 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