I'm trying to use a 'sumproduct' formula along the lines of:

=SUMPRODUCT(--(PRODUCT=$B$1),--(WAREHOUSE=10),PRODUCT_INV)

My problem is that in the range 'PRODUCT', there are cells with the #NAME error. This causes SUMPRODUCT to return the #NAME error as well.

Is there a way to get around this without having to alter the original data in anyway?