How to create a matrix in Excel?

Question

I have some data that looks a bit like this:

Value_1	Value_2	Value_3
TRUE	TRUE	FALSE
TRUE	TRUE	FALSE
TRUE	FALSE	TRUE

I want to create a matrix which counts the number of times each combination of two values are flagged as true on the same row. So the required output should look like this:

	Value_1	Value_2	Value_3
Value_1	3	2	1
Value_2	2	2	0
Value_3	1	0	1

Does anyone know how best to do this please? I've been fiddling around with pivot tables but haven't managed to make one that works as required.

Answer 1

If you have Dynamic array formulas, this will spill:

=MMULT(--TRANSPOSE(A2:C4),--A2:C4)

This is just matrix multiplication, but first the -- (double unary) converts the TRUE and FALSE to 1 and 0. Here's an example of the calculation of the 3.

---

Another option using SUM and INDEX/MATCH that can be dragged across and down.

=SUM(INDEX($A$2:$C$4,,MATCH(F$1,$A$1:$C$1,0))*INDEX($A$2:$C$4,,MATCH($E2,$A$1:$C$1,0)))