Specifying a Date Range in a SUMIFS Formula

Written by Allen Wyatt (last updated October 23, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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:


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 Microsoft 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. ...


Turning Track Changes Off for Selected Areas

Track Changes is a great tool to use so that you can, well, "track" what changes are made during the development of a ...

Discover More

Cell Address of a Maximum Value

Finding the maximum value in a range of cells is easy; finding the address of the cell containing that value is a ...

Discover More

Accepting All Formatting Changes

Tired of wading through a bunch of formatting changes when you have Track Changes turned on? Here's how to accept all ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Calculating the Day of the Year

Need to know what day of the year a certain date is? You can figure it out easily using the formulas in this tip.

Discover More

Phantom Counts

Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...

Discover More

Counting with Formulas

When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...

Discover More

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

View most recent newsletter.


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 seven more than 6?

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

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.