Count values based on both row and column condition in google spreadsheets

Question

I've got the following table:

Next to that I have the following table:

The value in column Q of the second table is calculated with the following function:

   =COUNTIF(INDEX(B:M,MATCH(P1,A:A,1),0),"1")

Right not I am counting the number of 1's based on the name in column A. I however wish to expand the function to only count the cells when the column value in the first row says 'Gruul / Mag', like it does here.

Does anyone know how I can get this done?

Answer 1

try:

=INDEX(QUERY(IFERROR(SPLIT(FLATTEN(IF(A3:A="",,A3:A&"×"&
 FILTER(B3:M*1, B1:M1="Grull / Mag"))), "×")), 
 "select Col1,sum(Col2)
  where Col2 is not null
  group by Col1
  label sum(Col2)''"))