Count number of occurrences of a string in a cell range matching column and row headers

Question

I have the following two tables in MS Excel.

I want to count the number of occurrences of 'AB' in the rows of 'Adam' and in the columns of 'Feb' and place in P2 cell.

Index and Match commands used as follows returns 'AC'. What is the best way to solve this?

=INDEX($B$3:$L$12,MATCH($N$2,$A$3:$A$12,1),MATCH($P$1,$B$1:$L$1,1))

Here is the file.

Answer 1

Have you tried using the SUMPRODUCT Function, it does not require CTRL SHIFT ENTER, so just copy the formula from here and paste it in the cell P2 and Fill down and fill across.

=SUMPRODUCT(($N$2=$A$3:$A$12)*($O2=$B$3:$L$12)*(P$1=$B$1:$L$1))

Answer 2

Try

=SUM(($B$3:$L$12=$O2)*($B$1:$L$1=$P$1)*($A$3:$A$12=$N$2))

may need array-entering pre Excel 365.