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
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
For more details on SUMPRODUCT
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
Ron Rosenfeld replied on 17-Oct-07 10:05 PM
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 -
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: