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

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

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

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

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

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

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

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

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

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

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)Hello,

I want to use Countifs function with Date and Time function. I have two different dates and I want to count how many operations (“reading”, “writing”,”dancing”etc) are between two dates and to make some kind of graffic with those dates. I used the following structure:

=Countifs(A4:A20, A20,C4:GL4,”>=”&date(year(b5),month(b5),day(b5)+time(hour(b5),minute(b5),0),c4:cgl4,”<"&date(year(c5),month(c5),day(c5)+time(hour(c5),minute(c5),0)

A – column with activities

c4:GL4 – column with hours (from 01:00 to 24:00 – each fifteen minutes)

b5 – beginning time

c5 – end of time

Thank you in advance!

Hello Mirela,

Thank you for sharing your query. It would be really great if you can send the workbook to [email protected] as It seems quite confusing to create a dataset with the information you shared. After that, I will try to solve your problem. Hope to hear from you soon.

Thanks!