SUMPRODUCT Returns #VALUE

Hi All,

I've worked out why SUMPRODUCT is returning # VALUE but I'm a little lost how to remedy it.

=SUMPRODUCT((E2:P2)*(E3:P3))/P7

From cells L2 to P2 and E3 to P3 although they have nothing in them there is a formulas which is causing the # VALUE, so just need a criteria to ignore these formula's if they a showing a blank cell.

Thanks

Instead of showing us a non-working formula, I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Try =SUMPRODUCT(E2:P2, E3:P3)/P7 . It is more efficient. And it ignores cells that do not contain numeric values.

Note: You might use semicolon instead of comma to separate parameters.

Originally Posted by joeu2004

Try =SUMPRODUCT(E2:P2, E3:P3)/P7 . It is more efficient. And it ignores cells that do not contain numeric values.

Note: You might use semicolon instead of comma to separate parameters.

Thank you joeu2004, worked perfectly.