SUMPRODUCT - where range has formula returned "" blank cells

In the table below, the blank cell D1 is not an empty cell, but driven from a formula such as =IF(G2="","",G2)


x y z x
1 1 1
2 1 3 3
3 3 4 5

When trying to use the formula =SUMPRODUCT((A2:D4)*(A1:D1="x")) I get #VALUE!

Is there any way to make this calculation work and ignore the blank cells

Part of the problem is the arrays in sumproduct need to have the same size and orientation. IE (A2:D4)*(B2:E4).

Also, whenever you will be using logical comparisons and text, use -- before the first array:
=SUMPRODUCT(--(

Here's one way:

=SUMPRODUCT(VALUE(0&A2:D4)*(A1:D1="x"))

The simplest solution would be:

Please Login or Register  to view this content.

Cheers,

Thanks Again Colin