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

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

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

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

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

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