Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Ignore Text
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Ignore Text
"Steve" wrote:
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) "Steve" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can SUMPRODUCT be made to ignore text | Excel Worksheet Functions | |||
How to ignore #DIV/0! in Sumproduct formula | Excel Worksheet Functions | |||
SUMPRODUCT - Ignore blank rows | Excel Worksheet Functions | |||
Sumproduct to ignore text and "" | Excel Worksheet Functions | |||
SUMPRODUCT excel ignore div/0 | Excel Programming |