Search code examples
excelexcel-formula

SUM of 2-way Weighted Grid in Excel

Example Hi friends, In the above example grid, I am looking for a formula or function that can find the sum of all Data elements with their weighted products. Below is an example pattern in terms of calculation -

Data Range: D5 : N7

Formula I am looking for is : Data (Range) * Weight A (In the same col) * Weight B (In the same row)

D5 * D4 * C5 + E5 * E4 * C5 ... + N5 * N4 * C5 +
D6 * D4 * C6 + .................+ N6 * N4 * C6 +
................................. N7 * N4 * C7

One way I know I could solve this is by calculating SUM at column and row levels and then adding them up to get a 'Grand Total'. However, I have a feeling there is a better and efficient way of solving this.

If anyone has any insights/suggestions, please help!

Solution

  • I think it's just

    =SUM(D5:N7*D4:N4*C5:C7)

    enter image description here

    I checked the total using

    =D$4*D5*$C5

    and copying down and across.