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.

**Table of Contents**hide

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

`=COUNTIFS(B5:G18,">="&I6,$B$5:$G$18,"<="&I9)`

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.

**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**)

`=COUNTIFS($B$5:$G$18,">="&I5,$B$5:$G$18,"<="&I5)`

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.

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

`=COUNTIFS($B$5:$B$16,">="&DATE(E5,1,1),$B$5:$B$16,"<="&DATE(E5,12,31))`

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

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

`=SUMPRODUCT(($B$5:$B$16>=DATE(E5,1,1))*($B$5:$B$16<=DATE(E5,12,31)))`

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.

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

`=COUNTIFS(C5:C18,J5,D5:D18,J6,E5:E18,J7,F5:F18,J8,B5:B18,">="&J9,B5:B18,"<="&J10)`

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.

**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**)

`=SUMIFS(G5:G18,C5:C18,J5,D5:D18,J6,E5:E18,J7,F5:F18,J8,B5:B18,">="&J9,B5:B18,"<="&J10)`

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.

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