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.

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 thisHow-To.

That seems to work quite well. Thank you so much for your prompt and knowledgeable response. It is greatly appreciated.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History