SUMIF with two columns

[Joseph N.]

[Excel 2003] Assume a worksheet with with numbers in column A; Boolean
criterion [Y or N] in column B; and another Boolean criterion [Y or N]
in column C. I would like to add the numbers in column A for all rows
that have 'y' in both columns B and C.

I have been trying to do this with a SUMIF statement in which the
criteria is a combination of the two columns. I have not been able to
find the magic formula, so either I am missing the correct syntax or
there is a better formula to use.

Help would be appreciated!

 

[Alan]

=SUMPRODUCT(--(A1:A25),--(B1:B25="Y"),--(C1:C25="Y"))
Regards,
Alan.

 

[Bernard Liengme]

The double negation in terms two and three are there to convert True and
FALSE to 1 and 0, repespectivley
So we do not need them in the first term
=SUMPRODUCT(A1:A25,--(B1:B25="Y"),--(C1:C25="Y"))
For more details on SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

=SUMPRODUCT(--(A1:A25),--(B1:B25="Y"),--(C1:C25="Y"))
Regards,
Alan.

[Excel 2003] Assume a worksheet with with numbers in column A; Boolean
criterion [Y or N] in column B; and another Boolean criterion [Y or N]
in column C. I would like to add the numbers in column A for all rows
that have 'y' in both columns B and C.

I have been trying to do this with a SUMIF statement in which the
criteria is a combination of the two columns. I have not been able to
find the magic formula, so either I am missing the correct syntax or
there is a better formula to use.

Help would be appreciated!

 

[Ron Rosenfeld]

[Excel 2003] Assume a worksheet with with numbers in column A; Boolean
criterion [Y or N] in column B; and another Boolean criterion [Y or N]
in column C. I would like to add the numbers in column A for all rows
that have 'y' in both columns B and C.

I have been trying to do this with a SUMIF statement in which the
criteria is a combination of the two columns. I have not been able to
find the magic formula, so either I am missing the correct syntax or
there is a better formula to use.

Help would be appreciated!

=SUMPRODUCT(A1:A1000*(B1:B1000="Y")*(C1:C1000="Y"))
--ron

 

[Ed Hansberry, MS-MVP/Mobile Devices]

[Excel 2003] Assume a worksheet with with numbers in column A; Boolean
criterion [Y or N] in column B; and another Boolean criterion [Y or N]
in column C. I would like to add the numbers in column A for all rows
that have 'y' in both columns B and C.

I have been trying to do this with a SUMIF statement in which the
criteria is a combination of the two columns. I have not been able to
find the magic formula, so either I am missing the correct syntax or
there is a better formula to use.

Help would be appreciated!

As others have suggested, creative use of the sumproduct()
formula can do it. FWIW, Excel 2007 introduced a new function
called sumifs() that handles it natively.

More on using sumproduct() for this purpose -
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -