Can SUMPRODUCT be made to ignore text

[Thanks]

Hi
My Sum range occasionally has text included in it, is there a a way to make
sum product ignore the test when evaluating?

 

[Stefi]

SUMPRODUCT works in this way without any additional measure (I tested it in
Excel 2003)!

Regards,
Stefi

„Thanks†ezt írta:

 

[Pete_UK]

Have you tried including a term like:

(ISNUMBER(sum_range))

?

Hope this helps.

Pete

 

[Thanks]

I guess when you break it down SUMPRODUCT works like A1*A2 where the
criteria are met. (The text value is not it the criteria)

If A1 = Dog andB1 =2 then A1*B1= #value, but
If A2 = 2 and B2 = 3 then A2*B2= 6

 

[Stefi]

Yes, but you asked SUMPRODUCT and not multiplier operator and
=SUMPRODUCT(A1:A2,B1:B2)
returns 6.

Stefi


„Thanks†ezt írta:

 

[Thanks]

Ok...If there is a text value in the ranges that are being multiplied I get a
#Value. How do I make the formula work in there is a text value in the range
that is being multiplied..

 

[Pete_UK]

Don't use the multiplier between terms in the SP formula. Use it like
this:

=SUMPRODUCT(--(condition_1),--(condition_2),--(condition_3),sum_range)

The conditions evaluate to TRUEs and FALSEs, the -- converts these to
1s and 0s, which then get multiplied.

Hope this helps.

Pete