SUMIF with a twist

[lukus2005]

I have a listing of projects with the following columns...

column C - contains an "X" when project is completed.

column D - contains value of contract.

column N - contains a "Y" if project loss money.

I want to add the value of all contracts that are complete (X) AND did
not lose money (Y).

I know how to use the SUMIF for the first part of the equation but to
omit the ones losing money I am not sure how or if I can use it in a
SUMIF statement.

 

[Dave Peterson]

=sumproduct(--(c1:c99="x"),--(n1:n99<>"y"),d1:d99)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


====
And if you're using xl2007, take a look at excel's help for =sumifs()

 

[lukus2005]

=sumproduct(--(c1:c99="x"),--(n1:n99<>"y"),d1:d99)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers.  The -- stuff changes truesand falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:http://www..xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html

====
And if you're using xl2007, take a look at excel's help for =sumifs()

Thanks Dave... that worked perfectly!

 

[herochenna]

Visit www.ExcelGoodies.Com

 

[herochenna]

Visit www.ExcelGoodies.Com