sumif or sumproduct with a left statement

[Eric D]

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

 

[John C]

=SUMPRODUCT((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfers!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.

 

[T. Valko]

In other words you want an OR condition:

=SUMPRODUCT(--((LEFT(C2:C10,6)="834097")+(LEFT(E2:E10,6)="834097")>0),H2:H10)

 

[Dave Peterson]

=SUMPRODUCT(
--((LEFT(Xfers!C:C,6)="834097")+(LEFT(Xfers!E:E,6)="834097")>0),
(Xfers!H:H))

 

[T. Valko]

i don't want it to count them twice.

That'll sum twice if C1 = x *and* E1 = x

=2*H1

 

[Eric D]

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