Excel - SUMIF with two columns

Asked By Joseph N. on 17-Oct-07 08:35 PM
[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 replied on 17-Oct-07 08:46 PM
=SUMPRODUCT(--(A1:A25),--(B1:B25="Y"),--(C1:C25="Y"))
Regards,
Alan.
Bernard Liengme replied on 17-Oct-07 09:44 PM
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
Ron Rosenfeld replied on 17-Oct-07 10:05 PM
=SUMPRODUCT(A1:A1000*(B1:B1000="Y")*(C1:C1000="Y"))
--ron
Ed Hansberry, MS-MVP/Mobile Devices replied on 18-Oct-07 12:27 PM
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? - http://mvp.support.microsoft.com/
K p replied on 03-Apr-09 08:09 AM
It a little longer but,what you can do is create a column D that equals  A&B&C and then do the sumif on column d.   Assume the value you want to sum is in e then the formula is:



Sumif (D1:D50,"YYY",E1:E50)