SUMIF Formula?

[Sho]

I have the following formula

=SUMIF(I2:I1319,"Manchester",T2:T1319)+SUMIF
(M2:M1319,"INT",T2:T1319)+SUMIF(N2:N1319,"NB",T2:T1319)

Unfortunately this sums everything at the moment. How can
I amend this formula so that if Manchester, INT and NB
appear on the same row (although in different columns) it
will sum, but not if say only Manchester and INT were in
the row. It should only calculate on seeing the 3 criteria
above.

I tried this formula but it did not work;

=SUMIF(AND
(I2:I1319="MANCHESTER",M2:M1319="INT",N2:N1319="NB"),T2:T13
19,0)))

I assume this sort of formula is not possible as it did
not work!.

Any help would be great

 

[Jason Morin]

=SUMPRODUCT((I2:I1319="Manchester")*(M2:M1319="INT")*
(N2:N1319="NB")*T2:T1319)

HTH
Jason
Atlanta, GA

 

[Ann Scharpf]

Does this formula need to have the unary operator minus
signs? I.e.
=SUMPRODUCT(--(I2:I1319="Manchester")*--(M2:M1319="INT")*
--(N2:N1319="NB")*T2:T1319)

I only just learned about these double-minus signs in the
Newsgroups yesterday. But I thought they were necessary
to change the "TRUE" to a "1" and enable multiplication.

Ann

 

[Peo Sjoblom]

If you use them you shouldn't use the multiplication operator <*>

=SUMPRODUCT(--(I2:I1319="Manchester"),--(M2:M1319="INT"),--(N2:N1319="NB"),T
2:T1319)

should work, the whole idea by converting the TRUE/FALSE to numbers is that
you can use sumproduct's
built in [sumproduct(range,range)], with a comma. You can also use

=SUMPRODUCT((I2:I1319="Manchester")*(M2:M1319="INT")*(N2:N1319="NB"),T2:T131
9)

it's all about the nano-seconds <g>, the first formula is somewhat faster

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Norman Harker]

Hi Ann!

Not so worried about the absence of -- as about the use of the three
multiplications which could fall down if T2:T1319 contains text.

=SUMPRODUCT(--(A1:A100="John"),--(B1:B100="Salary"),C1:C100)
Returns same as:
=SUMPRODUCT((A1:A100="John")*(B1:B100="Salary"),C1:C100)
But:
=SUMPRODUCT((A1:A100="John")*(B1:B100="Salary")*C1:C100)
Returns #VALUE if the summed range contains text