If TABLE1!N2:N5 does not house any error values...

Invoke SumProduct with the comma syntax:

=SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)+0,TABLE1!N2:N5)

Otherwise, you have to switch to:

=SUM(IF(TABLE1!J2:J5='Summary Results
PC'!B13,IF(ISNUMBER(TABLE1!N2:N5),TABLE1!N2:N5)))

which must be confirmed with control+shift+enter.

philcud wrote:
> hi all,
> i have the following formula
>
> =SUMPRODUCT((TABLE1!J2:J5='Summary Results PC'!B13)*(TABLE1!N2:N5))
>
> in effect this is a simple sumif, the reason i'm using sumproduct is
> that i am going to expand it to more than one criteria.
>
> my problem lies in the range i am summing (TABLE1!N2:N5), contains text
> and error values (first example in cell j5, if i shrink the range to
> only look down to cell j4, it works)
>
> i have tried using
>
> '=SUMPRODUCT((TABLE1!J2:J5=='Summary Results
> PC'!B$13)*ISNUMBER((TABLE1!N2:N5)))
>
> but this gives me the count of number cells in the range, not the
> summed range needed.
>
> have googled this for wuite some time and have come up with no solution
> - over to the group.
>
> Thanks in advance.
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.