SUMPRODUCT()'s arguments aren't the problem. Since the ranges are
multiplied, they don't have to be balanced, since only the result of the
multiplication (in this case a rectangular range) is the argument to
SUMPRODUCT, not the individual ranges.

However, since the ranges are of different size (e.g, the Row, A1:AS1 is
4 columns wider than the block E2:AS1512), the multiplication of these
ranges will result in #N/A's filling the last 4 columns of the resulting
array, and therefore SUMPRODUCT() will also return #N/A.

This change works:

=SUMPRODUCT(('Discussed v Opened'!C2:C1512=Summary!A3) *
ISNUMBER(SEARCH(" - D",'Discussed vs Opened'!E1:AS1)) * ('Discussed vs
Opened'!E2:AS1512="Yes"))

but I don't know what the OP intended with the extra columns...



The In article ,
"Bernie Deitrick" <deitbe @ consumer dot org wrote:

One thing that immediately jumps out is that your ranges aren't balanced ---
one is a row, one is a
column, and one is a block! SUMPRODUCT requires ranges of equal size, and of
only 1 dimenstion (Row
or column).