# Excel sumproduct if December 10, 2010 at 13:39:37
Specs: Windows Vista
 Hello, I am using the following formula to calculate a weighted average of data from 2 columns in 50 rows --=SUMPRODUCT(A1:A50,B1:B50)/SUM(A10:A50)How do I change this formula to add a condition from another column? The formula should calculate the weighted averages only for rows in which a condition from column C is met. The condition is that the cell in column C should NOT have the word "open". For example, IF cells 1-10 in column C say "open", then it should calculate the weighted average using only rows 11-50.Thanks for your help. See More: Excel sumproduct if

#1 December 10, 2010 at 14:14:46
 I'm not 100% sure what you are looking for but this syntax will not calculate the SUMPRODUCT portion for any rows where the value in Column C of that row contains Open:=SUMPRODUCT((A1:A50)*(B1:B50)*(C1:C50<>"Open"))This will not sum any cells in A10:A50 where C contains Open:=SUMIF(C10:C50,"<>Open",A10:A50)Putting them both together (if that's what you are looking for) gets you this:=SUMPRODUCT((A1:A50)*(B1:B50)*(C1:C50<>"Open"))/SUMIF(C10:C50,"<>Open",A10:A50)Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#2
December 10, 2010 at 14:29:50
 That seems to work quite well. Thank you so much for your prompt and knowledgeable response. It is greatly appreciated.

Report •

#3 December 10, 2010 at 18:32:13
 Anytime!Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions 