Microsoft Office Tutorials and References
In Depth Information
Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()
AVERAGEIFS() , and
When someone sees how easy using SUMIF() is, they invariably want the
function to do more. One of the most frequent questions at the MrExcel mes-
sage board is along the lines of this: “ I am using SUMIF() to get a total by
region. How can I put two conditions in there to only get the total for a certain
region and product? ” In legacy versions of Excel, there were ways to do this,
but they were difficult. You had to use either SUMPRODUCT(), DSUM(), or an
array formula. There is a lot of complexity in going from a simple SUMIF() to
the intricate Boolean logic required to understand SUMPRODUCT().
, and COUNTIFS()
The order of the arguments differs between SUMIF and SUMIFS. The
sum_rangeis the first argument in SUMIFS but the third argument in
SUMIF. It seems pretty common that you would be editing a SUMIF
function to add additional conditions. Remember to move the sum_range
to be the first argument when you are moving from SUMIF to SUMIFS.
Thankfully, Excel 2007 added plural versions of SUMIF(), COUNTIF(), and
AVERAGEIF() that can handle not just two conditions, but up to 127 condi-
tions. The three new functions add the letter S to the end of the function name
(that is, SUMIFS(), COUNTIFS(), and AVERAGEIFS()) to signify that multiple
IFs are being considered. With SUMIFS() and AVERAGEIFS(), you first specify
the range to be summed or averaged. You then specify pairs of arguments. In
each pair, you first specify the range to check and then the value to match
in that range. The following sections describe these three functions.
The SUMIFS() function adds the cells in a range that meet multiple criteria.