Home » Forums » Open Source Learning Center » Question and Answer

Sumproduct with Sumif

Submitted by jsmmao on 9 December, 2014 - 14:25

Hello all - new to the forum here and look for some help with a formula I am struggling with. I am looking to get a weighted average based on 2 different columns with a lookup on a variable in a 3rd column. For example.

A B C
Text 1 100 80
Text 1 80 20
Text 2 150 30

What I am trying is to match to a text value in column A and then take the weighted average of the values in column C based on the values in column b.

Currently, I am using =SUMPRODUCT(A:A="Text 1",C:C,B:B)/SUM(B:B)

Is what I am trying to achieve in possible? I get #DIV/0 every time I try and have tried numerous different ways.

Any help would be greatly appreciated. I can upload a spreadsheet if that helps. Thanks

‹ Heavy file while empty Excel Duplicates ›
»

Hi jsmmao Try the

Submitted by mec1981 on 17 December, 2014 - 06:18.

Hi jsmmao

Try the following:

=(SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))/SUM(B:B)

I find that you have to define the limits for sumproduct and not just use a whole column (could be wrong here). anyway I have based this on your data being in rows 2:100 so adjust accordingly, also not 100% sure I got your drift so the above will add all column C values that have matching Column A Text, then divide by total of column B

Regards
Mark

»