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

Report •

#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


Ask Question