Is it possible to sum all weighted values without adding helper columns?

I have the following two tables set up:

A table of survey questions

In reality it has a lot more questions, but this should be enough for demonstration purposes.

In addition I have a table with all the answers and "subjects" (each respondent answers each question concerning N subjects):

Now.. what I would normally do is add an xlookup/index-match column to the response-table (last one) to pull each weight, multiply with the score, and then work from there.

I wonder, however, if there is a more elegant solution that does not require this.

What I want is to have a table that does summarizes the data by

1. multiply each question by its weight (using some lookup)
2. group by subject and group (and sum values)
3. Present in following table:

Preferably in one formula. I think this should be possible by using the filter function, xlookup, or similar, but I have been stumped in both my own attempts and by my googling skills.

Thanks in advance..

Edit: Tried the below method. I get correct values for questions in group 1, but 0 for the others. I inspected the matrices produced in a, b, c, d there and they are as follows:

a:

b:

c:

d:

I believe the problem occurs in the SUM(IFERROR(d*c;0)), because in this example the first 3 rows of c are all errors, and the d only has values in the first 3 rows, so those will be 0, and when c gets to its values, d has run out.

Link to example workbook:
Workbook

If i am not wrong this should work for you, using <kbd>FILTER( )</kbd> Function

• Formula used in cell <kbd>J2</kbd>

=LET(
     a,FILTER($B$2:$C$9,$A$2:$A$9=$I2),
     b,FILTER($F$2:$G$6,J$1=$E$2:$E$6),
     c,INDEX(TAKE(a,,-1),XMATCH(TAKE(b,,1),TAKE(a,,1))),
     d,INDEX(TAKE(b,,-1),XMATCH(TAKE(a,,1),TAKE(b,,1))),
     SUM(IFERROR(d*c,0)))