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.

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

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

Count date occurrence -COUNTIFS Date Range

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

Result

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.

Items on a date-COUNTIFS Date Range

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

Result

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.

Dataset 2From 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.

Year occurrence -COUNTIFS Date Range

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.

Result

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.

sumproduct -COUNTIFS Date Range

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.

Result

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.

occurrence multiple criteria-COUNTIFS Date Range

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

Result

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.

sumifs multiple criteria-COUNTIFS Date Range

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

Result

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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

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

  2. Greetings Jessica,

    Use the SUMIFS function to impose multiple criteria like Product Category, Product, and Dates to find out the total sales of products (i.e., Chocolate Chip or Carrot).

    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)

    Find Sales

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

    Regards
    Maruf Islam (Exceldemy Team)

Leave a reply

ExcelDemy
Logo