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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History