Excel Sum and Count in Date RangeExamples show how to sum amounts or count items in Excel, based on a date range. Use SUMIFS function and COUNTIFS function to calculate the totals. 
Excel Date Range Sum or CountThe examples below show how to sum values, or count items, in Microsoft Excel, based on a date range. The formulas use functions that allow 2 or more criteria: To find items in a date range, each formula will need at least 2 criteria:
More criteria can be added, if needed. See the example below, which uses 3 criteria:
Sum Amounts in Date RangeTo sum amounts based on a date range, you can use the SUMIFS function. SUMIFS is similar the older Excel SUMIF function, which only allows one criterion.
This video shows the steps for setting up the SUMIFS formula, to get the total number of units sold in a date range. The written steps, and the full video transcript, are below the video. Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript One of the most common things you do in Excel is get a total of something. So here we have a column where we've sold items on certain dates and we can see how many units we sold each day. We have a total at the bottom, and that just uses the SUM function. If you want to put some conditions on the total, instead of using SUM, you could use a SUMIFS function. This is available in Excel 2007 and later versions. In this cell I'd like to see the total for a specific date range. I've put a starting date here and an ending date. In this cell I'm going to put in a SUMIFS formula, and it will look at the dates and then only total the units that are between the start and end date. To start the formula, I'll click in this cell, type an equal sign and the SUMIFS function, and open bracket. The first thing we have to put in is the sum range, so which cells do we want to add up if we meet the criteria? We want to add these units sold. Put that in, and put in a comma. The next thing is the criteria range. Which cells do we want to check to see if they meet the criteria that we've set. And that's the date cells. I'll select those. Type a comma. The next thing is the first criteria. We want it to be based on this start date. We want any numbers that are for a date on or after this start date. I'm going to put the operator in, and the operator will be greater than or equal to, and I have to put that inside quotes. Double quote, greater than, equal to, double quote. I'm going to join that to a cell reference. I'll put in an ampersand and then click on the start cell, so it's going to look for any items where the date is on or after that start date. Then we're going to have to put in our end date. For the criteria range 2, again, we want it to check those dates, type a comma. The second criteria is this end date. We want things that are on or before that. Again, the operator will go in double quotes, and this will be less than, equal to, double quote, and we'll join with the ampersand. Click on the end date, and close the bracket. Press Enter. We can see that there were 494 units sold in this date range. To verify that quickly, we can manually select the dates in that date range. The first one within that date range is January 9th going down to February 4th. When I look in the status bar, the sum is 494, which matches our total. So with SUMIFS you can use multiple criteria to get a total. For more Excel tips and tutorials and to download the sample file from this video, please visit my Contextures website at www.contextures.com 
Put Dates on WorksheetFor formulas that use a date range, it's best to put the start and end dates in cells on the worksheet. You can refer to those cells in your formula, and it's easy to change the date range later, when you need to report on a different time period. In the screen shot below, the start and end dates are in the light blue cells:
Those cells were used in the video above, and in the SUMIFS formula in the next section. In the data table shown below:
Because the data is in a named Excel table, this is a dynamic range, and it will adjust automatically, if records are added or removed. 
SUMIFS FunctionThe next step will be to enter a SUMIFS function formula in cell D5, to get the total number of units sold in a specific date range, based on the start and end dates in D2 and E2. You can see the formula in the next section, and first, here are the requirements, when using the SUMIFS function. For the SUMIFS function, there are 3 required arguments:
Optional Criteria ArgumentsIf needed, you can add more pairs of criteria ranges and criteria.
Our formula will use that optional pair of arguments for the second criteria. 
SUMIFS Formula for Date RangeHere is the SUMIFS formula that is entered in cell D5, to get total units sold in the date range:
NOTE: There are details on how the formula works, below the screen shot. 
How the SUMIFS Formula WorksThis formula uses the 3 required arguments, and one pair of optional criteria arguments.
To help you follow the details on how the formula works, I've coloured the cell references to match the cell references in the screen shot above. SUMIFS Arguments in FormulaThe formula uses the three required arguments, and one pair of optional arguments (in square brackets)
1) sum_range The formula should return the total number of units sold, by adding up the numbers in the UnitsSold column, for rows that meet all of the criteria: $B$2:$B$9 Note: Because the data is in a named Excel table, this is a dynamic range, and the cell references in the formula will adjust automatically, if records are added or removed. 2) criteria_range1 First, the formula will check the values in the Date column: $A$2:$A$9 3) criteria1 The first criterion uses an operator combined with a cell reference: ">=" & $D$2
4) [criteria_range2] Next, the formula will check the values in the Date column again: $A$2:$A$9 5) [criteria2] The second criterion also uses an operator combined with a cell reference: "<=" & $E$2

Check SUMIFS Formula ResultFor the current date range, January 6th to March 8th, the total number of units sold is 375. To check that formula result, follow these steps:
NOTE: You can see more information on the Excel Status Bar on the Microsoft site. 
Sum and Criteria Range ShapesIn the SUMIFS formula, there are a couple of rules for the range shapes:
Usually, the ranges are in a vertical list or table, with one column, and multiple rows, for each range. Horizontal ranges are allowed too, like this list, where each range has one row, and multiple columns. Ranges with multiple rows AND multiple columns are allowed too, like this layout, where each range has two rows, and 4 columns. 
COUNTIFS  Count in Date RangeIf you need to count the number of cells, based on one or more criteria, use the COUNTIFS function.
In the example below, the formula will count the rows where the date (column A) is within our set date range.

COUNTIFS FunctionWith the COUNTIFS function, there are 2 required arguments in its syntax:
Optional Criteria ArgumentsIf needed, you can add more pairs of criteria ranges and criteria.
Criteria Range Rules
COUNTIFS Formula for Date RangeHere is the COUNTIFS formula that is entered in cell D5, to get total units sold in the date range:
NOTE: There are details on how the formula works, below the screen shot. 
How the COUNTIFS Formula WorksThis formula uses the 2 required arguments, and one pair of optional criteria arguments.
To help you follow the details on how the formula works, I've coloured the cell references to match the cell references in the screen shot above. COUNTIFS Arguments in FormulaThe formula uses the two required arguments, and one pair of optional arguments (in square brackets)
The formula should return the total number of dates, in the specified range, that occur in the range of dates between the start date and the end date. Dates that fall on the start date or end date will be included in the count. Note: Because the data is in a named Excel table, this is a dynamic range, and the cell references in the formula will adjust automatically, if records are added or removed. 1) criteria_range1 First, the formula will check the values in the Date column: $A$2:$A$9 2) criteria1 The first criterion uses an operator combined with a cell reference: ">=" & $D$2,
3) [criteria_range2] Next, the formula will check the values in the Date column again: $A$2:$A$9 4) [criteria2] The second criterion also uses an operator combined with a cell reference: "<=" & $E$2

Check COUNTIFS Formula ResultFor the current date range, January 6th to March 8th, the total number of orders is 3. It's easy to check that count manually, in this small example. For a larger data set, you can follow these steps:

Criteria Range ShapesIn the COUNTIFS formula, there are a couple of rules for the range shapes:
Here are the criteria ranges for the Region and Date Range formula, in the More Criteria section, below. Each range is 1 column, and 8 rows. Other range shapes are permitted too, such as horizontal, and blocks with multiple rows and multiple columns. See examples in the SUMIFS Range Shapes section, above. 
Another COUNTIFS ExampleThis video shows how to use the COUNTIFS function to count cells based on a range of numbers. The minimum and maximum numbers are entered on the worksheet, and the formula is similar to the date range formula shown above. Note: There is a full transcript available to read, below the video. Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript Video Transcript: Count Numbers in a RangeIn Excel 2003 and earlier versions, if you want to count things based on criteria you can use COUNTIF In Excel 2007 and later versions, there's also a new function called COUNTIFS and with this you can do some things a little more easily. So, if we wanted to count items that have quantity that's between 5 and 10, in the old version we had to combine two different COUNTIFs Start the FormulaTo get our answer with COUNTIFS, we create one formula, and we can put different sets of ranges and criteria We can have up to 127 of those combinations, so we get quite a bit of flexibility. In this case, we've got COUNTIFS, where we're looking at this range of cells, and saying, I want:
So we've combined the two criteria And when we press Enter, we get 6 as a result Add More CriteriaWe can add more criteria as well, and in this case, we could look for items where there's a pen in the order. So I've added another set of criteria, so the third set is looking at range A2 to A10, and finding items where there's a pen. And when we press Enter, there are only 2 now, where
Use WildcardsYou can also use wildcards, and in this formula, instead of just typing "pen", we typed "pen*", with an asterisk. So we get anything that starts with "pen" So now there are 4, because we're getting "pen" and "pencil" items, where the quantity is between 5 and 10 
More Criteria for SUMIFS or COUNTIFSThe SUMIFS and COUNTIFS examples above both used 2 criteria  Start Date and End Date. More criteria can be added, if needed, and this example shows totals in a date range for a specific region. The following criteria are entered on the worksheet, and referenced in the formulas:
Here is the SUMIFS formula from cell E5:
And here is the COUNTIFS formula from cell F5:
In both formulas, the final two arguments:
Get Sample FileTo see the drop down list example from this page, download the Sum and Count in Date Range workbook. The zipped file is in xlsx format, and does not contain any macros. 
More TutorialsCount Criteria in Other Column 
Last updated: October 21, 2022 7:17 PM