Sumproduct Ignore Text

[Steve]

Hello,
I am using a sumprouct formula to add the contents in G2:G66 where the
first 8 characters of C2:C66 are Subtotal.
=SUMPRODUCT(--(LEFT(C2:C66,8)="Subtotal",--(G2:G66))
My understanding is that bu using the coma rather than * would ignore
text values in G2:G66. However, I am still getting #value be casue of
text values. What is the fix?
Thanks!

 

[Claus Busch]

Hi Steve,

Am Fri, 1 Jun 2012 07:53:30 -0700 (PDT) schrieb Steve:

I am using a sumprouct formula to add the contents in G2:G66 where the
first 8 characters of C2:C66 are Subtotal.
=SUMPRODUCT(--(LEFT(C2:C66,8)="Subtotal",--(G2:G66))

try:
=SUMPRODUCT(--(LEFT(C2:C66,8)="Subtotal"),(G2:G66))

Regards
Claus Busch

 

[joeu2004]

I am using a sumprouct formula to add the contents
in G2:G66 where the first 8 characters of C2:C66
are Subtotal.
=SUMPRODUCT(--(LEFT(C2:C66,8)="Subtotal",--(G2:G66))

You are missing a parenthesis. But that seems to be just a posting typo.
Next time, copy-and-paste from the Formula Bar.

In any case, the formula can be written a little more cleanly, thus:

=SUMPRODUCT(--(LEFT(C2:C66,8)="Subtotal"),G2:G66)

My understanding is that bu using the coma rather
than * would ignore text values in G2:G66. However,
I am still getting #value be casue of text values.

Because when you write --(G2:G66), SUMPRODUCT no longer sees G2:G66
directly. Instead, you are telling Excel to perform an arithmetic operation
(double negation) and pass an array of numeric values to SUMPRODUCT.

Only use double negation -- or some other arithmetic operation -- when you
want to convert non-numeric values to numeric values. For
example, --(LEFT(C2:C66,8)="Subtotal") converts an array of TRUE and FALSE
to an array of 1 and 0, which SUMPRODUCT needs to see in order to perform
the "logic" that you intend.