Apply Sumprod on mix text & numbers arrays

[Nir]

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]

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}

 

[JMB]

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

array entered with Cntrl+Shift+Enter

 

[Bob Phillips]

=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)

 

[JMB]

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