Microsoft

Use SUMIFS() to sum by multiple conditions in Excel

Excel's SUMIF() lets you sum values, conditionally. It's new sibling, SUMIFS() lets you sum values by multiple conditions.

This blog post was originally published in November 2011.

Summing values that correspond to a specific date is a common Excel task and easily accomplished using SUMIF(). This function totals values that meet a specific condition-a single condition. It won't work if you need to sum values that fall within a specific time period. You could resort to an array, but the newer SUMIFS() function is easier than an array. This function (new to Excel 2007) totals values that meet more than one condition. That makes it the perfect solution for summing values in a time period.

SUMIFS() uses the following syntax:

SUMIFS(sumrange,criteriarange1,criteria1[,criteriarange2,criteria2[,...]])

where sumrange is the contiguous set of values you're summing, criteriarangex represents the possible conditional values, and criteriax expresses the condition. What makes this function even more flexible is that criteriarange and criteria don't have to be the same type of values or operators from set to set.

Before we take on an example, let's briefly review SUMIF(). Specifically, let's use it to sum units sold for a particular date in the sheet below. To do so, you'd use the following function:

=SUMIF(A6:A13,"="&B1,C6:C13)

which returns 106, the sum of 87 and 19. This function sums values in A6:A13 where the date value in column A equals the date value in B1.

To expand the sum to include values sold during a specific time period, you'll need SUMIFS(). The function in B4 illustrates SUMIFS() expanded functionality:

=SUMIFS(C6:C13,A6:A13,">="&B1,A6:A13,"<="&B2)

In this case, the function sums the values in C6:C13, where the corresponding date values in A6:A13 are equal to or greater than 11/9 and equal to or less than 11/11.

Specifying a criteria range for each criteria might seem inefficient, but it actually makes the function more flexible. For example, suppose you want to narrow the total to a time period for a specific person. That criteria isn't in column A, but that's Okay. In this case, you'd simply add a third criteria set, as follows:

=SUMIFS(C6:C13,A6:A13,">="&B1,A6:A13,"<="&B2,B6:B13,B3)

The first two criteria sets still filter the values to a specific time period. The third set narrows that sum to only those values that correspond to Bill (B3).

Consider using the SUMIFS() function instead of an equivalent array function. In addition, I recommend that you replace any existing arrays if you upgrade to 2007 or 2010. The SUMIFS() function can handle up to 29 criteria sets.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

13 comments
DonG43
DonG43

Thanks for describing a new function in Excel. So often we overlook the new functions because we are used to doing something one way with a prior version of Excel. Also, your examples are easy to follow and very intuitive.

Edward D
Edward D

In the first example -- for SUMIF() -- you made the criteria "="&B1 But in the third example, you made criteria3 B3. Why was it not "="&B3 ? Is this a difference related to data type, such as text versus numeric value? Thank you for the Excel feature report.

wbobrowski
wbobrowski

COUNTIF and AVERAGEIFS are great ones too! Thanks for the article.

DAS01
DAS01

"each criteria" Tsk tsk. The very common error: "criteria" is plural. The singular is "criteriON".

itdept
itdept

Above the first screenshot, I believe you flipped the A range and the C range in the formula.

david.hanshumaker
david.hanshumaker

Since the 3rd argument is a logical value, as long as the criteria range is the same for multiple criteria, why not nest the AND function inside the SUMIFS function, where the individual criteria are the arugments for the AND. For older versions without SUMIFS, that should work to permit multiple criteria with the same criteria range in the SUMIF function, right?

aksalaymeh
aksalaymeh

SORRY for typing mistakes,here the corrected one Thanks for the good tips Don't you think it is better to attach an example sheet to be downloaded like the word example !!?

aksalaymeh
aksalaymeh

Thanks for the good tips Don't you think it is better to attach an example sheet to be downkoaded kike the word example !!?

john.a.wills
john.a.wills

but informaticians should be language-conscious enough to use "criterion" as the singular and "criteria" as the plural.

zimmerwoman
zimmerwoman

Susan: I used the wizard and this is what my formula looked like: "=SUMIFS($C$6:$C$13,$A$6:$A$13,D$1)". (the quotation marks are not part of the formula as it appears in the cell.) I made the ranges absolute so I could enter all the days, sum the totals and enter a cross-foot sum funtion of all days to check the total against the total of sales detail by-person-by-day. What I don't get is the syntax of your formula. I copied it all into excel and can see that it works. I just don't understand this portion: "="&B1. I wasn't able to set up new conditions myself using that syntax. So if I want to use your form and data I am good to go. If I want to use this in another context, I am sunk. Anywhere you can point me to get a good explanation of this apparent portion of your formula?

ssharkins
ssharkins

Criteria has been the accepted form for both in the technical world for a long, long time -- I haven't used the word criterion, or had an editor correct it, in fifteen years. I do understand your comment though -- I was loathe to accept function names as verbs -- xoring... that one really hurt. :)

Editor's Picks