Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
I currently have the following formula :
=SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
=SUMPRODUCT((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfe rs!E:E,6)="834097"),(Xfers!H:H))
Use the + (or) in your sumproduct. I am assuming that since you are using whole column references that you have xl2007. Hope this helps. -- John C "Eric D" wrote: I currently have the following formula : =SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
In other words you want an OR condition:
=SUMPRODUCT(--((LEFT(C2:C10,6)="834097")+(LEFT(E2:E10,6)="834097 ")0),H2:H10) -- Biff Microsoft Excel MVP "Eric D" wrote in message ... I currently have the following formula : =SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
=SUMPRODUCT(
--((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfers!E:E,6)=" 834097")0), (Xfers!H:H)) Eric D wrote: I currently have the following formula : =SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
i don't want it to count them twice.
That'll sum twice if C1 = x *and* E1 = x =2*H1 -- Biff Microsoft Excel MVP "John C" <johnc@stateofdenial wrote in message ... =SUMPRODUCT((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfe rs!E:E,6)="834097"),(Xfers!H:H)) Use the + (or) in your sumproduct. I am assuming that since you are using whole column references that you have xl2007. Hope this helps. -- John C "Eric D" wrote: I currently have the following formula : =SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif or sumproduct with a left statement
Thank you all - Valko and Dave that's what i needed
John yours worked too but counted column H twice when 834097 appeared in both, but thanks anyway -- Eric "Dave Peterson" wrote: =SUMPRODUCT( --((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfers!E:E,6)=" 834097")0), (Xfers!H:H)) Eric D wrote: I currently have the following formula : =SUMPRODUCT(--(LEFT(Xfers!C:C,6)="834097"),(Xfers!H:H)) which this works great for items occurring in column c, but i also need it to sum column H when column E's first 6 characters = 834097 but i don't want it to count them twice. So if column C and E match then it shouldn't sum column H twice. Can someone help me with this? Thanks -- Eric -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum if statement with a left statement | Excel Discussion (Misc queries) | |||
SUMPRODUCT AND LEFT | Excel Worksheet Functions | |||
Sumif/vlookup/left | Excel Discussion (Misc queries) | |||
SUMIF and LEFT and Named Ranges | Excel Worksheet Functions | |||
Left and Sumproduct | Excel Discussion (Misc queries) |