Need help with sumproduct weighed average

[krh96011]

I am trying to write a formula that will ignore 0 or blanks with the sumproduct function. My current formula is =SUMPRODUCT(F47:F53,G47:G53)/SUM(G47:G53)

Now I need to ignore 0 or blanks in this formula to make it work correctly. Can someone help?

 

[AhoyNC]

See if this does what you want.

Excel Workbook
	F	G	H	I
47	0.25	5		0.456667
48	0.5	10
49	0.85	3
50	0	6
51		0
52	0.36	6
Sheet

 

[Aladin Akyurek]

@ AhoyNC

A lesser specification would seem also to work:

=SUMPRODUCT($F$47:$F$53,$G$47:$G$53)/SUMIFS($G$47:$G$52,$F$47:$F$52,">0")

 

[AhoyNC]

@Aladin
I agree that's a better formula. If the OP could have a negative number in column F I think your formula would need to change to:

=SUMPRODUCT($F$47:$F$53,$G$47:$G$53)/SUMIFS($G$47:$G$52,$F$47:$F$52,[COLOR=#ff0000]"<>0"[/COLOR])

 

[krh96011]

@AhoyNC
Thanks for your response. I wound up using this formula

=SUMPRODUCT(--(F40:F43>0),F40:F43,G40:G43)/SUMIF(F40:F43,">0",G40:G43)

 

[Aladin Akyurek]

@AhoyNC
Thanks for your response. I wound up using this formula

=SUMPRODUCT(--(F40:F43>0),F40:F43,G40:G43)/SUMIF(F40:F43,">0",G40:G43)

Why? Don't you have SUMIFS? Also, ou can skip the --(F40:F43>0) bit in SUMPRODUCT.