Can SUMPRODUCT be made to ignore text

  • Thread starter Thread starter Thanks
  • Start date Start date
T

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?
 
SUMPRODUCT works in this way without any additional measure (I tested it in
Excel 2003)!

Regards,
Stefi

„Thanks†ezt írta:
 
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
 
Yes, but you asked SUMPRODUCT and not multiplier operator and
=SUMPRODUCT(A1:A2,B1:B2)
returns 6.

Stefi


„Thanks†ezt írta:
 
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..
 
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
 
Back
Top