How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)

Often while working in a dataset, we need to find, calculate, or match anything based on a certain condition. In this article, we demonstrate how to fetch values using COUNTIFS date range.

In the above dataset, we have Product Sales on different days. We want to find various values and a constant condition will be always imposed i.e. date.

Download Excel Workbook

6 Easy Ways to Use COUNTIFS with Date Range

Method 1: Count Date Occurrence

The COUNTIFS function returns a value considering single or multiple criteria. We use COUNTIFS for the date range to count days when sales occur in the dataset. The syntax of the COUNTIFS function is

 COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

In that case, we can have a Starting Date (i.e.,11/1/2021) and an End Date (i.e.,12/31/2021) to count the sales in between.

Step 1: Write the following formula in any blank cell (i.e., I12).

Inside the formula,

B5:G18=range1

“>=”&I6=criteria1; matches dates equal or greater than I6.

$B$5:$G$18=[range2]

“<=”&I9=[criteria2]; matches dates equal or less than I9.

As we count the days when sales occur between two dates, we insert only two ranges and criteria. You can use yours as per your dataset demands.

Step 2: Hit ENTER, you’ll get the number of days between given cell references(i.e., I6 and I9).

If you want to cross-check the occurrences, you simply find the day’s number is 14 by just looking at the dataset.

Read More: How to Count Date Occurrences in Excel (3 Suitable Examples)

Method 2: COUNTIFS Date Range for Items on a Date

We mentioned earlier in Method 1 that the COUNTIFS function is a versatile function. We can count any instances that occur on a date.

Let’s say we want to count how many sales occur on a date (i.e., 11/6/2021).

Step 1: Paste the below formula in any cell (i.e., J5)

Inside the formula,

$B$5:$G$18=range1

“>=”&I5=, criteria1; matches dates equal or greater than I5.

$B$5:$G$18=[range2]

“<=”&I5=, [criteria2]; matches dates equal or less than I5.

 Step 2: Press ENTER then the sales occurrence number on a date (i.e.,11/6/2021) appears.

For finding sales or any kind of occurrence number on a date in any dataset, you can use this method with ease.

Read More: Excel Formula to Count Days from Date (5 Easy Methods)

Method 3: Year Occurrence

From previous instances, we see how the COUNTIFS function uses a date range to fetch date, item occurrences. In this case, we demonstrate how the COUNTIFS function can count the year occurrences from a date range. To demonstrate the steps, we use a simpler dataset as shown in the following picture.

From the dataset, we want to count how many times a year occurs in the dataset.

Step 1: Type the following formula in any adjacent cell (i.e., F:G5).

 Inside the formula,

$B$5:$B$16 refers to the date range

“>=”&DATE(E5,1,1) and “<=”&DATE(E5,12,31) refer a whole year for every cell reference (i.e.,E5). COUNTIFS matches the dates within the year E5.

Step 2: Press ENTER then Drag the Fill Handle afterwards the number of any year occurrence appears in the cells similar to the following image.

If you cross-check the results, you’ll find exactly the same numbers as the formula results in.

Read More: How to Calculate Years Between Two Dates in Excel (2 Methods)

Similar Readings

• Calculate Working Days Excluding Sundays in Excel
• [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
• How to Calculate Days Outstanding in Excel (With Easy Steps)
• Calculate Years and Months between Two Dates in Excel (6 Approaches)
• How to Add Days to a Date in Excel Excluding Weekends (4 Ways)

Method 4: SUMPRODUCT Counts COUNTIFS Date Range for Occurrence

The SUMPRODUCT function can mimic exactly the same result as the COUNTIFS function does in the previous method (i.e., Method 3). We can use any dataset to demonstrate the similarities between SUMPRODUCT and COUNTIFS function in their outcomes.

Though the SUMPRODUCT takes multiple arrays as arguments and COUNTIFS takes multiple criteria ranges, criteria as arguments, they result in the same value type.

The syntax of the SUMPRODUCT function is

SUMPRODUCT(array1, [array2], [array3], ...)

 We use arrays to insert multiple criteria inside the SUMPRODUCT function.

Step 1: Paste the below formula in cell F:G5.

In the formula,

>=DATE(E5,1,1) indicates the E5 year’s beginning.

<=DATE(E5,12,31) indicates the E5 year’s ending.

At last, the formula matches each entry whether it is within the year (i.e., E5) or not, and returns the matches number.

Step 2: Hit ENTER after that Drag the Fill Handle to come up with the occurrences of any year referred to inside the formula as shown in the below picture.

Read More: How to Create a Day Countdown in Excel (2 Examples)

Method 5: Count Occurrence with Multiple Criteria

As we know the COUNTIFS function takes multiple criteria, we use it to find out the occurrence of a sale of a particular product imposing multiple conditions. In this case, we want the number of sales of Product Chocolate Chip in the East Region’s Boston City under the Cookies Category.

Step 1: Write the below formula in any cell (i.e., J12).

Inside the formula,

C5:C18, D5:D18, E5:E18, F5:F18, and B5:B18 refer to the criteria range.

J5, J6 ,J7, J8, “>=”&J9, and “<=”&J10 refer to the criteria.

The formula matches the criteria in every criteria range then returns the number of occurrences.

Step 2: Press ENTER, in a moment you’ll see the occurrence times in cell J12 similar to the following image.

You can use as many criteria as your dataset demands and easily end up with desired results.

Read More: How to Apply Excel Formula to Count Days from Date to Today

Method 6: Find Total Amount Using SUMIFS with MULTIPLE Criteria

Similar to the previous method (i.e., Method 5), we can mimic the calculation using SUMIFS functions but one step further. In doing so, we want to get the Quantity of a Product that abides by multiple criteria. We want the Quantity of Chocolate Chip from East City Boston of Cookies Category within the date 11/18/2021 to 12/30/2021.

The syntax of the SUMIFS function is

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Step 1: Type the below formula in any cell (i.e., J12)

Inside the formula,

G5:G18=sum_range

C5:C18, D5:D18, E5:E18, F5:F18, B5:B18, and B5:B18 refer to the criteria range.

J5, J6, J7, J8, “>=”&J9, and “<=”&J10 refer criteria.

In the end, the formula fetches the total sum of products that satisfy the criteria.

Step 2: Hit ENTER then the sum amount of Quantity appears in the cell as shown in the following picture.

You can impose any conditions as criteria and the formula just works fine.

Read More: How to Find Number of Weeks Between Two Dates in Excel

Conclusion

COUNTIFS for date range offers numerous conditions to match by. In this article, we demonstrate 6 core methods to go by. Functions like SUMPRODUCT and SUMIFS work similarly to COUNTIFS in outcomes, differences between them can be the topic of another article. Hope you find the above-described methods useful and worthy. Comment, if you have further queries or some to add. You can read my other articles on the ExcelDemy website.

Related Articles

• How to Calculate Working Days between Two Dates in Excel (4 Methods)
• Excel Count Months in a Column( 4 Quick Ways)
• How to Count Months in Excel(5 ways)
• Calculate Tenure in Years and Months in Excel
• How to Calculate Expiration Date with Excel Formula
• How to Calculate Working Capital Days in Excel (with Easy Steps)