Search code examples
excelexcel-formulaexcel-match

How to calculate a weighted product with index and match?

The following sheet shows the data I have. The main idea is to sum up every student's grade, based on a set of weighted criteria.

Cells C9:G11 are populated with data from the list A1:B4, in which every element (Excellent to Poor) is related to a value (10 to 4).

I used the following formula to calculate the value of C12 (and C13 etc.). However, it is too naive. How could this calculation be improved?

=INDEX($B$1:$B$4;MATCH(C9;$A$1:$A$4;0)) + INDEX($B$1:$B$4;MATCH(C10;$A$1:$A$4;0)) + INDEX($B$1:$B$4;MATCH(C11;$A$1:$A$4;0))

Sample Excel Sheet

UPDATE: I would like to consider the weighted calculation.

=INDEX($B$1:$B$4;MATCH(C9;$A$1:$A$4;0)) * B9 + INDEX($B$1:$B$4;MATCH(C10;$A$1:$A$4;0)) * B10 + INDEX($B$1:$B$4;MATCH(C11;$A$1:$A$4;0)) * B11

Sample Excel Sheet with Weights

Solution

  • Use SUMPRODUCT(SUMIF())

    =SUMPRODUCT(SUMIF($A$1:$A$4,C$9:C$11,$B$1:$B$4))

    enter image description here

    per your update:

    =SUMPRODUCT(SUMIF($A$1:$A$4,C$9:C$11,$B$1:$B$4)*$B$9:$B$11)

    enter image description here