Excel - Sumproduct Ignore Text

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!

Hi Steve,

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


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

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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)


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.