Specifying a Date Range in a SUMIFS Formula

by Allen Wyatt
(last updated October 23, 2021)

1

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 Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Counting with PivotTables

One of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique ...

Discover More

Creating the 'Mils' Symbol

Different industries use their own terminologies and symbols. In the military, one symbol is referred to as the "mils" ...

Discover More

Using AutoCorrect

The AutoCorrect feature in Excel is a great tool for quickly entering information. Here's an explanation of the feature ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Median of Selected Numbers

Need to find a median value in a series of values? It's easy with the MEDIAN function. What isn't as easy is to derive ...

Discover More

Specifying a Language for the TEXT Function

You may want to use Excel to display dates using a different language than your normal one. There are a couple of ways ...

Discover More

Finding the Date Associated with a Negative Value

When working with data taken from the real world, you often have to determine which certain conditions were met, such as ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 1?

2021-10-25 08:26:18

Mike D.

Out of curiosity I wanted to know if I added to the length of the list if the formula would automatically adjust. I first tried by copying the the last row, by dragging the corner arrow, and as I thought it did not.
I then formatted the data as a table, then added to the table and voila, the formula (not included inside the table) adjusted to the new size of the table.
It worked with both cell reference and range names.
Cool Beans ! ! !


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.