Contextures

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.

Totals for Date Range

The examples below show how to sum amounts, or count items, in 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, the formulas 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. 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 are below the video.

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

start and end dates on worksheet

SUMIFS Function

The next step is to enter a formula with the SUMIFS function, to get the total number of units sold in a date range, based on the dates in D2 and E2.

  • Numbers to sum are in the UnitsSold column (B)
  • Dates to check are in column A

With 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 criteria
  3. 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
  • 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)

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

  1. sum_range - $B$2:$B$9, with the UnitsSold numbers
  2. criteria_range1 - $A$2:$A$9, is the range to check for start dates
  3. criteria1 - ">=" & $D$2, greater than or equal to the Start date in cell D2
  4. criteria_range2 - $A$2:$A$9, is the range to check for end dates
  5. criteria2 - "<=" & $E$2, less than or equal to the End date in cell E2

SUMIFS formula for date range

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 a count, based on one or more criteria, use the COUNTIFS function.

In this example, 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:

  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)

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

  1. criteria_range1 - $A$2:$A$9, is the range to check for start dates
  2. criteria1 -             ">=" & $D$2, greater than or equal to Start date in cell D2
  3. criteria_range2 -$A$2:$A$9, is the range to check for end dates
  4. criteria2 -            "<=" & $E$2, less than or equal to End date in cell E2

COUNTIFS formula for date range

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.

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

 

Get weekly Excel tips from Debra

 

Last updated: July 14, 2021 7:17 PM
Contextures RSS Feed