Search code examples
excelexcel-formulaworksheet-function

How to get weighted sum depending on multipliers in column in Excel?

I have the table in Excel:

enter image description here

In column C (Sum) I want to get sum this way:

  • If in column A or B value is 1 then take Amount 48 and multiply by Multiplier (1) = 2.
  • If in column A or B value is 0 then take Amount 48 and multiply by Multiplier (0) = 1,5.
  • Then K1 and K2 summed.

So for row 2 the result in column C will be: 48*2 + 48*2 = 192.
For row 5 the result in column C will be: 48*1,5 + 48*2 = 168.

Is it possible to automate this process using Excel formula for C column (inspite of number of columns)?

Solution

  • Or you could use Countif (no shorter though)

    =COUNTIF(A2:D2,0)*I$2*I$1+COUNTIF(A2:D2,1)*I$3*I$1

    enter image description here