I just posted some hours ago a similar problem and I got satisfactory
answers.
That's why I am encouraged to ask a new question that is closer to my
actual problem.

Here it goes.

In a range I have a list of expenses per department:

Allocation table 1 2
Dpt Expense1 Expense 2
a 1 3
b 2 4
c 3 5
a 4 1
c 5 7

Say that Allocation tables are in B1:C1.
Say that expenses are in A3:C7 (excluding headers)

As you can see, one department can have multiple expenses
An allocation table is a table that describes how the expenses of the
departments are allocated
to the end users, which are in turn the departments themselves.

In another range, I have the tables accordint to which expenses are
allocated:

1 a b
a 0.3 0.7
b 0.1 0.9
c 1 0

Say it is in A21:C23, excluding headers


2 a b
a 0.6 0.4
b 0.25 0.75
c 0.5 0.5

Say it is in A31:C33, excluding headers


Now, suppose that I want the total expense after allocation for each
Dept in each expense (A9='a', A10='b')?
The formulas I need should be in B9:C10

For B9 (dept='a', expense1) the result should be:
1*0.3+2*0.1+3*1+4*0.3+5*1 = 9.7
For C10 (dept='a', expense2) the result should be:
3*0.6+4*0.25+5*0.5+1*0.6+7*0.5 = 9.4

Of course, my model is a lot bigger and more complex. I expect to
input a formula in B9 and copy it down and right to C10. I would not
like to use auxiliary cells for intermedite results.

Any help is highly appreciated.