Hi,
I want to apply sumproduct on mixed text & numbers arrays, expecting result
only when valid multipluy occurs

{1,a,3,1,b},{2,2,1,c,1}={4}

"Nir" wrote:

Hi,
I want to apply sumproduct on mixed text & numbers arrays, expecting result
only when valid multipluy occurs

{1,a,3,1,b},{2,2,1,c,1}={4}

=SUM(IF(ISNUMBER(A1:A5*B1:B5),A1:A5*B1:B5))

array entered with Cntrl+Shift+Enter



"Nir" wrote:

Hi,
I want to apply sumproduct on mixed text & numbers arrays, expecting result
only when valid multipluy occurs

{1,a,3,1,b},{2,2,1,c,1}={4}

=SUMPRODUCT(A1:A5,B1:B5)

works fine, but gives 5 not 4.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nir" wrote in message
...
Hi,
I want to apply sumproduct on mixed text & numbers arrays, expecting
result
only when valid multipluy occurs

{1,a,3,1,b},{2,2,1,c,1}={4}

I got 5 too, but worked harder for it. Forgot sumproduct treats non-numeric
as 0. Thanks for the reminder.

"Bob Phillips" wrote:

=SUMPRODUCT(A1:A5,B1:B5)

works fine, but gives 5 not 4.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Nir" wrote in message
...
Hi,
I want to apply sumproduct on mixed text & numbers arrays, expecting
result
only when valid multipluy occurs

{1,a,3,1,b},{2,2,1,c,1}={4}