How to get weighted sum depending on two conditions in Excel?

Question

I have this table in Excel:

I am trying to get weighted sum depending on two conditions:

• Whether it is Company 1 or Company 2 (shares quantity differ)
• Whether column A (Company 1) and column B (Company 2) has 0 or 1 (multipliers differ)

---

Example:
Lets calculate weighted sum for row 2:

Sum = 2 (multiplier 1) * 50 (1 share price) * 3 (shares quantity for Company 1) +

+0.5 (multiplier 0) * 50 (1 share price) * 6 (shares quantity for Company 2) = 450

So, Sum for Row 2 = 450.

---

For now I am checking only for multipliers (1 or 0) using this code:

=COUNTIF(A2:B2,0)*$B$9*$B$8 + COUNTIF(A2:B2,1)*$B$9*$B$7

But it does not take into account the shares quantities for Company 1 or Company 2. I only multiply 1 share price with multipliers, but not with shares quantity).

---

How can I also check whether it is Company 1 or Company 2 in order to multiply by corresponding Shares quantity?

---

Upd:

Rasmus0607 gave a solution when there are only two companies:

=$B$9*$E$8*IF(A2=1;$B$7;$B$8)+$B$9*$E$9*IF(B2=1;$B$7;$B$8)

Tom Sharpe gave a more general solution (number of companies can be greater than 2)

---

I uploaded my Excel file to DropBox: Excel file

Answer 1

I can offer a more general way of doing it with the benefit of hindsight that you can apply to more than two columns by altering the second CHOOSE statement:-

=SUM(CHOOSE(2-A2:B2,$B$7,$B$8)*CHOOSE(COLUMN(A:B),$E$8,$E$9))*$B$9

Unfortunately it's an array formula that you have to enter with CtrlShiftEnter. But it's a moot point whether or not it would be better just to use one of the other answers with some repetition and keep it simple.

You could also try this:-

=SUMPRODUCT(N(OFFSET($B$6,2-A2:B2,0)),N(OFFSET($E$7,COLUMN(A:B),0)))*$B$9

Here's how it would be for three companies

=SUM(CHOOSE(2-A2:C2,$B$7,$B$8)*CHOOSE(COLUMN(A:C),$F$8,$F$9,$F$10))*$B$9

(array formula) or

=SUMPRODUCT(N(OFFSET($B$6,2-A2:C2,0)),N(OFFSET($F$7,COLUMN(A:C),0)))*$B$9