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

Hi there! Yes, there is a way to make SUMPRODUCT ignore text when evaluating. You can use the ISNUMBER function to check if each cell in the sum range contains a number or not. Here's how you can do it:

1. In a blank cell, enter the formula =ISNUMBER(A1), where A1 is the first cell in your sum range.
2. Copy the formula down to all the cells in the sum range.
3. Now, in your SUMPRODUCT formula, replace the sum range with the range of cells containing the ISNUMBER formulas. For example, if your original formula was =SUMPRODUCT(A1:A10,B1:B10), and your ISNUMBER formulas are in cells C1:C10, your new formula would be =SUMPRODUCT(C1:C10,B1:B10).

By using the ISNUMBER function, any cell in the sum range that contains text will return a FALSE value, which will be treated as a zero in the SUMPRODUCT calculation. This way, the text will be ignored and only the numbers will be added up.

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

Regards,
Stefi

€žThanks€� ezt Ã*rta:

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

Have you tried including a term like:

(ISNUMBER(sum_range))

?

Hope this helps.

Pete

On Sep 4, 12:40*pm, Thanks wrote:
Hi
My Sum range occasionally has text included in it, *is there a a way to make
sum product ignore the test when evaluating?

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" wrote:

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

Regards,
Stefi

€žThanks€� ezt Ã*rta:

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

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

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




"Stefi" wrote:

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

Regards,
Stefi

€žThanks€� ezt Ã*rta:

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

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..

"Stefi" wrote:

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

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




"Stefi" wrote:

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

Regards,
Stefi

€žThanks€� ezt Ã*rta:

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

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

On Sep 4, 2:24*pm, Thanks wrote:
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..



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

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

"Stefi" wrote:

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

Regards,
Stefi

„Thanks” ezt írta:

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

- Show quoted text -