SUMPRODUCT Ignore blanks

[onthegreen03]

Hi - I am using a SUMPRODUCT formula to calculate a weighted average and I want to modify it so it ignores cells that are blank. My formula is below ... the blank cells could appear in column E. Thanks as always for the help!

=SUMPRODUCT($M$6:M$1005,E$6:E$1005/SUM($E$6:$E$1005))

 

[jasonb75]

Blanks have no numeric value so ignoring them will make no difference to the result.

 

[onthegreen03]

But when I run the formula as is I get a #VALUE! error. When I modify it to only include the rows currently with data (through row 229) I get the right result. How can I avoid getting the error and still include the entire range down to row 1005?

 

[jasonb75]

There was no mention of errors in your original post, that tells me that your blanks are formulas rather than just blank / empty cells.

=SUMPRODUCT($M$6:M$1005,IFERROR(E$6:E$1005/SUM($E$6:$E$1005),0))

 

[onthegreen03]

Beautiful, thanks. Sorry about not mentioning that column E did include formulas. Appreciate the help!