# 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)

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

##### MORE FROM ALLEN

Centering Information in Table Cells

One of the most common ways to format information in a table is to apply some sort of alignment to the contents of table ...

Discover More

Getting the Name of the Worksheet Into a Cell

Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in ...

Discover More

Exiting a For ... Next Loop Early

If you use For ... Next loops in your macros, make sure you give a way to jump out of the loop early. That way you can ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

##### More ExcelTips (ribbon)

Using GEOMEAN with a Large List

When performing a statistical analysis on a large dataset, you may want to use GEOMEAN to figure out the geometric mean ...

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

Counting Displayed Cells

When you filter data, Excel displays only a portion of what is really in a worksheet. If you want to count the number of ...

Discover More
##### Subscribe

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

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 nine minus 3?

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.

##### Subscribe

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