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


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

Automatically Converting to GMT

You know what time it is, right? (Quick—look at your watch.) What if you want to know what time it is in Greenwich, ...

Discover More

Filtering for Comments (Notes)

Excel makes it easy to filter a data table based on various values in that table. It isn't so easy to filter according to ...

Discover More

Changing Text in Text Boxes on a Chart

Macros allow you to make changes to virtually anything you can see in Excel. This tip examines how to make changes (even ...

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)

Returning an ANSI Value

Need to know the character value of the first character in a string? It's easy to do, without using a macro, by using the ...

Discover More

Using the INT Worksheet Function

The INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics ...

Discover More

Returning the MODE of a Range

The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the ...

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 2 + 8?

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.