Sumproduct weighted average with one condition

[excelman2]

Hi,

I've tried a few suggestions, but I cannot figure out how to add a condition to this formula: =SUMPRODUCT(G2:G100,F2:F100) / SUM(F2:F100) so that it will automatically recalculate when I add additional data. How I would rewrite it (but this doesn't work of course) is like this:

IF(c2:c100="GE"),SUMPRODUCT(G2:G100,F2:F100) / SUM(F2:F100)

I admit it, I'm not expert but thought that would help you see what I'm trying to do; I use the formula to calculate the weighted average of multiple stock purchases/dividend reinvesting of the same stock over time:

Column G: Stock Purchase Price
Column F: Number of Shares
Column C: Stock Ticker Symbol (GE for example); there are multiple stock tickers--not just GE.
Column C on a worksheet 2: Stock Ticker Symbol (GE for example)

Thank you in advance!

 

[Marcelo Branco]

Hi and welcome to the board,

Maybe this

=SUMPRODUCT(--($C$2:$C$100="GE"),$F$2:$F$100,$G$2:$G$100)/SUMIF($C$2:$C$100,"GE",$F$2:$F$100)

Alternatively you can use a cell reference instead of hard coding the stock symbol.

HTH

M.

 

[excelman2]

Marcelo,

You're a genius! Thanks so much for the help and welcome; this has been driving me crazy and it works great in Excel. Sadly, I can't say the same for Google Docs, but I can export and import the data as a work around. Better than doing it by hand!

Tyler

 

[Marcelo Branco]

Marcelo,

You're a genius! Thanks so much for the help and welcome; this has been driving me crazy and it works great in Excel. Sadly, I can't say the same for Google Docs, but I can export and import the data as a work around. Better than doing it by hand!

Tyler

Tks for the feedback. Glad to help

M.

 

[heatherT]

I found this formula very helpful, it worked perfectly! However, I need one that can consider 3 criteria when determining which values to include in the weighted average calculation. Building on your previous example, lets assume the 2 additional criteria are in columns A and B

 

[Marcelo Branco]

If i'm understanding correctly what you need, maybe something like this

=SUMPRODUCT(--($A$2:$A$100="criteria1"),--($B$2:$B$100="criteria2"),--($C$2:$C$100="criteria3"),$F$2:$F$100,$G$2:$G$100)/
SUMIFS($F$2:$F$100,$A$2:$A$100,"criteria1",$B$2:$B$100,"criteria2",$C$2:$C$100,"criteria3")

Hope this helps

M.

 

[buchi]

If i'm understanding correctly what you need, maybe something like this

=SUMPRODUCT(--($A$2:$A$100="criteria1"),--($B$2:$B$100="criteria2"),--($C$2:$C$100="criteria3"),$F$2:$F$100,$G$2:$G$100)/
SUMIFS($F$2:$F$100,$A$2:$A$100,"criteria1",$B$2:$B$100,"criteria2",$C$2:$C$100,"criteria3")

Hope this helps

M.

That really is a smart trick, thanks!