Contextures

Home > Formulas > Date Range

Excel Sum and Count in Date Range

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

status bar sum matches formula result

Excel Date Range Sum or Count

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

  • SUMIFS - total sum based on multiple criteria
  • COUNTIFS - total count based on multiple criteria

To find items in a date range, each formula will need at least 2 criteria:

  1. Start Date
  2. End Date

More criteria can be added, if needed. See the example below, which uses 3 criteria:

  1. Start Date
  2. End Date
  3. Region Name

Sum Amounts in Date Range

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

If 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

   ✅ Click here for 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 Worksheet

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

  • Start date is in cell D2
  • End date is in cell E2

Those cells were used in the video above, and in the SUMIFS formula in the next section.

In the data table shown below:

  • dates start in cell A2 and end in cell A9
  • units sold start in cell B2 and end in cell B9

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.

start and end dates on worksheet

SUMIFS Function

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

  1. sum_range - cells with the numbers to add up
  2. criteria_range1 - cells to check for first criteria
  3. criteria1 - first criteria to match
Optional Criteria Arguments

If needed, you can add more pairs of criteria ranges and criteria.

  • In the screen shot below, the first argument has been entered in the formula
  • The pop-up tooltip shows the SUMIFS function arguments
  • I've highlighted the first optional criteria argument - criteria_range2
  • Note: Optional arguments are enclosed in square brackets

Our formula will use that optional pair of arguments for the second criteria.

SUMIFS function arguments

SUMIFS Formula for Date Range

Here is the SUMIFS formula that is entered in cell D5, to get total units sold in the date range:

  • =SUMIFS($B$2:$B$9, $A$2:$A$9, ">=" & $D$2, $A$2:$A$9, "<=" & $E$2)

NOTE: There are details on how the formula works, below the screen shot.

SUMIFS formula for date range

How the SUMIFS Formula Works

This formula uses the 3 required arguments, and one pair of optional criteria arguments.

  • =SUMIFS($B$2:$B$9, $A$2:$A$9, ">=" & $D$2, $A$2:$A$9, "<=" & $E$2)

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 Formula

The formula uses the three required arguments, and one pair of optional arguments (in square brackets)

  1. sum_range - $B$2:$B$9
  2. criteria_range1 - $A$2:$A$9
  3. criteria1 - ">=" & $D$2
  4. [criteria_range2] - $A$2:$A$9
  5. [criteria2] - "<=" & $E$2

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

  • The operator, ">=" must be enclosed in double quote marks
  • This tells Excel that the value must be greater than or equal to the Start date
  • The Start Date is in cell D2, and an absolute reference is used for that cell: $D$2
  • The & operator (ampersand operator) combines the operator with the cell reference

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

  • The operator, "<=" must be enclosed in double quote marks
  • This tells Excel that the value must be less than or equal to the End date
  • The End Date is in cell E2, and an absolute reference is used for that cell: $E$2
  • The & operator (ampersand operator) combines the operator with the cell reference

Check SUMIFS Formula Result

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

  • Select cells B3:B5. Those 3 dates are within the start and end dates.
  • In the Status Bar, at the bottom of Excel, check the Sum
  • It should match the formula result in cell D5

NOTE: You can see more information on the Excel Status Bar on the Microsoft site.

status bar sum matches formula result

Sum and Criteria Range Shapes

In the SUMIFS formula, there are a couple of rules for the range shapes:

  • All sum and criteria ranges must be the same size (same number of rows and columns)
  • Each sum or criteria range must be a contiguous block of cells

Usually, the ranges are in a vertical list or table, with one column, and multiple rows, for each range.

vertical sum and criteria ranges

Horizontal ranges are allowed too, like this list, where each range has one row, and multiple columns.

horizontal sum and criteria ranges

Ranges with multiple rows AND multiple columns are allowed too, like this layout, where each range has two rows, and 4 columns.

horizontal sum and criteria ranges

COUNTIFS - Count in Date Range

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

  • Start date is in cell D2
  • End date is in cell E2

start and end dates on worksheet

COUNTIFS Function

With the COUNTIFS function, there are 2 required arguments in its syntax:

  1. criteria_range1 - cells to check for criteria
  2. criteria1 - criteria to match
Optional Criteria Arguments

If needed, you can add more pairs of criteria ranges and criteria.

  • In this screen shot, the criteria_range2 argument is highlighted
  • That pair of arguments is optional, which is indicated by the square bracket
  • COUNTIFS function arguments
Criteria Range Rules
  • All criteria ranges must be the same size (same number of rows and columns)
  • Each criteria range must be a contiguous block of cells

COUNTIFS Formula for Date Range

Here is the COUNTIFS formula that is entered in cell D5, to get total units sold in the date range:

  • =COUNTIFS($A$2:$A$9,">=" & $D$2, $A$2:$A$9, "<=" & $E$2)

NOTE: There are details on how the formula works, below the screen shot.

COUNTIFS formula for date range

How the COUNTIFS Formula Works

This formula uses the 2 required arguments, and one pair of optional criteria arguments.

  • =COUNTIFS($A$2:$A$9,">=" & $D$2, $A$2:$A$9, "<=" & $E$2)

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 Formula

The formula uses the two required arguments, and one pair of optional arguments (in square brackets)

  1. criteria_range1 - $A$2:$A$9
  2. criteria1 -   ">=" & $D$2
  3. [criteria_range2] -$A$2:$A$9
  4. [criteria2] -   "<=" & $E$2

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,

  • The operator, ">=" must be enclosed in double quote marks
  • This tells Excel that the value must be greater than or equal to the Start date
  • The Start Date is in cell D2, and an absolute reference is used for that cell: $D$2
  • The & operator (ampersand operator) combines the operator with the cell reference

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

  • The operator, "<=" must be enclosed in double quotation marks
  • This tells Excel that the value must be less than or equal to the End date
  • The End Date is in cell E2, and an absolute reference is used for that cell: $E$2
  • The & operator (ampersand operator) combines the operator with the cell reference

Check COUNTIFS Formula Result

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

  • Select cells B3:B5. Those 3 dates are within the start and end dates.
  • In the Status Bar, at the bottom of Excel, check the Count or Numerical Count
  • That number should match the formula result in cell D5

status bar sum matches formula result

Criteria Range Shapes

In the COUNTIFS formula, there are a couple of rules for the range shapes:

  • All criteria ranges must be the same size (same number of rows and columns)
  • Each criteria range must be a contiguous block of cells

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.

vertical sum and criteria ranges

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 Example

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

If 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

   ✅ Click here for Transcript

Video Transcript: Count Numbers in a Range

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

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

  • quantities that are greater than or equal to the number in E4
  • AND, they also have to be less than or equal to the number that's in G4

So we've combined the two criteria

And when we press Enter, we get 6 as a result

Add More Criteria

We 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

  • the quantity is between 5 and 10
  • AND the item sold was a pen

Use Wildcards

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

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

  1. Start Date - cell D2
  2. End Date - cell E2
  3. Region Name - cell F2

Here is the SUMIFS formula from cell E5:

  • =SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $E$2, $A$2:$A$9,"<=" & $F$2, $C$2:$C$9,$G$2)

And here is the COUNTIFS formula from cell F5:

  • =COUNTIFS($A$2:$A$9,">=" & $E$2, $A$2:$A$9,"<=" & $F$2, $C$2:$C$9,$G$2)

In both formulas, the final two arguments:

  • check the region names in column C
  • see if they match the region name entered in cell G2.

formulas with 3 criteria

Get Sample File

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

Sum Functions

Count Functions

Count Criteria in Other Column

Count Specific Items

Count Cells With Specific Text

Functions List

About Debra

 

Last updated: October 25, 2023 3:12 PM