Software optimize

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.

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.

2 comments
karimsisi
karimsisi

Exaclty what I was looking for, thanks. This is what I was looking for ">="&B1, placing the greater than and equal sign in quotation marks and concatenating it with an ampersand to my formula.

I want to learn more about criteria in conditional functions in Excel. Can you direct me to an article or write about it? For example, how to tell sumifs to do something if the cell is empty? Two consecutive quotation marks should do the trick, but it doesn't.

karimsisi
karimsisi

UPDATE: I was doing something wrong and that is why the double quotation marks were not working. Working fine now. Nevertheless, I would like to read more as mentioned above if possible.