Specifying a Date Range in a SUMIFS Formula

Thomas has a worksheet that contains, in column A, a date for each record in the worksheet. In cells G2 and G3 he has, respectively, a beginning date and an ending date. Thomas would like to reference these cells in a SUMIFS formula so that it returns only the sum of cells in column D for records that, in column A, have a date between what is in G2 and G3. However, Thomas can't seem to put the SUMIFS formula together properly to get the correct result.

The key to this is to understand the syntax for the SUMIFS function:

=SUMIFS(sum_range, criteria_range, criteria)

In other words, you only need a minimum of three parameters with the SUMIFS function. You can, though, specify additional criteria, and that is done by adding additional "criteria_range, criteria" pairs to the formula. In Thomas's case, there are two criteria he has, so his syntax for SUMIFS would look similar to this:

=SUMIFS(sum_range, criteria1_range, criteria1, criteria2_range, criteria2)

With this understood, it is simply a matter of plugging in the correct ranges and criteria specifications. Let's say that Thomas only has records in rows 2 through 50, with row 1 being used for column headings. In this case, plugging in the ranges would look like this:

=SUMIFS(D2:D50, A2:A50, criteria1, A2:A50, criteria2)

Note that the criteria ranges, in Thomas's case, are both the same, as he is wanting to compare dates (column A) for both checks. Now it is a matter of putting together the criteria. Here is where it can get a bit tricky. The criteria need to include comparison operators of some sort (less than, equal, or greater than) and these need to be specified as text values. So, for instance, Thomas might be tempted to specify his criteria in this manner:

=SUMIFS(D2:D50, A2:A50, ">G2", A2:A50, "<G3")

This won't work, however, because you've included "G2" and "G3" within the quote marks. This means that Excel treats them as actual text values, not as cell values, which is what you need. So, you can move them outside of the quote marks in this manner:

=SUMIFS(D2:D50, A2:A50, ">" & G2, A2:A50, "<" & G3)

This grabs the values in G2 and G3 and concatenates them with the comparison operators in the criteria statements. The result is that you will get the proper comparisons and, thus, the proper result from SUMIFS.

If you wanted to, you could make the formula a bit more understandable if you use some named ranges in your worksheet. You could, for instance, select all the dates in column A and give that range the name "Dates" and give the values in G2 and G3 the names "StartDate" and "EndDate," respectively. Your formula could then be entered in this manner:

=SUMIFS(D2:D50, Dates, ">" & StartDate, Dates, "<" & EndDate)

You could, of course, also create a named range for the column you want to sum and adjust the formula accordingly. The benefit of using named ranges in this manner is that your formula is more understandable when you examine it a week, month, or years from now, when you may have forgotten what each cell or range is used for.

Finally, the examples used for SUMIFS in this tip assume that you want to sum values that are greater than the starting date (G2) and less than the ending date (G3). If you want that range to be inclusive—meaning, to include any records for dates specified in G2 and G3—then you'll want to change the comparisons specified in the criteria:

=SUMIFS(D2:D50, Dates, ">=" & StartDate, Dates, "<=" & EndDate)

For additional examples of using the SUMIFS function, you may find this page on the Microsoft support site helpful:

https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11876) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.