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.

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:

To find items in a date range, the formulas need at least 2 criteria:

- Start Date
- End Date

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

- Start Date
- End Date
- Region Name

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.

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.

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:

**sum_range**- cells with the numbers to add up**criteria_range1**- cells to check for criteria**criteria1**- criteria to match

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

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:

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

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.

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.

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.

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

With the COUNTIFS function, there are 2 required arguments:

**criteria_range1**- cells to check for criteria**criteria1**- criteria to match

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

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

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

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

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.

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.

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.

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:

- Start Date - cell D2
- End Date - cell E2
- 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.

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.

Count Criteria in Other Column

Count Cells With Specific Text

Last updated: July 14, 2021 7:17 PM

Contextures RSS Feed