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.

**Read More:**Â **How to Count Date Occurrences in Excel**

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

**Read More:**** How to Calculate Years in Excel from Today (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**.

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

**Related Content: The DateDiff Function in Excel VBA (5 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**).

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

What would you do if you wanted to know how many chocolate chip and carrot cookies were sold between two dates?

Greetings

Jessica,Use

the SUMIFS functionto impose multiple criteria like ProductCategory,Product, andDatesto find out the total sales of products (i.e.,Chocolate ChiporCarrot).For

Chocolate Chips`=SUMIFS(G4:G17,E4:E17,J4,F4:F17,J5,B4:B17,">="&J6,B4:B17,"<="&J7)`

For

Carrot`=SUMIFS(G4:G17,E4:E17,M4,F4:F17,M5,B4:B17,">="&M6,B4:B17,"<="&M7)`

You can also add all the different sales afterward. Hope, this may help you.

Regards

Maruf Islam(Exceldemy Team)