Tanjima Hossain
If you are looking for ways to use the COUNTIF function with multiple criteria date range in Excel, then this article will be helpful for you. Sometimes you may want to count the dates based on multiple criteria rather than a single criterion. So, let’s get into the main article to know the details by going through the following 6 examples.
Download Workbook
6 Examples to Use COUNTIF Function with Multiple Criteria Date Range in Excel
Here, in the following dataset, we have some sales records with their corresponding selling dates. By using the following 6 examples we will count dates depending on different criteria.
We have used Microsoft Excel 365 version for creating this article. However, you can use any other version at your convenience.
Example-1: Counting Number of Different Dates Using COUNTIF Function with Multiple Criteria Date Range
Here, we will be counting two dates 1/9/2021 and 2/25/2021 (mm/dd/yyyy format) among the dates of the Date range. These two criteria will be applied with the help of the COUNTIF function.
- Type the following formula in cell E8.
=COUNTIF(C5:C15,"1/9/2021")+COUNTIF(C5:C15,"2/25/2021")Here, C5:C15 is the date range, and “1/9/2021”, and “2/25/2021” are the multiple criteria.
Formula Breakdown
- COUNTIF(C5:C15,”1/9/2021″) → returns the number of times the date 1/9/2021 is presented in the range.
- Output → 2
- COUNTIF(C5:C15,”2/25/2021″) → returns the number of times the date 2/25/2021 is presented in the range.
- Output → 2
- COUNTIF(C5:C15,”1/9/2021″)+COUNTIF(C5:C15,”2/25/2021″) → becomes
- 2+2 → 4
- After pressing ENTER, you will get 4 as the total number of presence of the specified dates.
Read More: How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
Example-2: Applying SUMPRODUCT & COUNTIF Functions for Multiple Criteria in a Date Range
A similar task in Example-1 can be done using the combination of the SUMPRODUCT and COUNTIF functions.
- Apply the following formula in cell E8.
=SUMPRODUCT(COUNTIF(C5:C15,{"1/9/2021","2/25/2021"}))Here, C5:C15 is the date range, and “1/9/2021”, and “2/25/2021” are the multiple criteria.
Formula Breakdown
- COUNTIF(C5:C15,{“1/9/2021″,”2/25/2021”}) → returns an array containing the number of presence of the dates “1/9/2021”, and “2/25/2021” in the date range.
- Output → {2,2}
- SUMPRODUCT({2,2}) → adds the values up inside the array
- Output → 4
- After pressing ENTER, you will get 4 as the total number of presence of the specified dates.
Note: You have to press CTRL+SHIFT+ENTER instead of pressing ENTER while using any other versions except for Microsoft Excel 365.
Example-3: Counting Numbers Between Two Dates
In this section, we will try to count the number of dates from the month of March presented in the Date column.
- Type the following formula in cell E8.
=COUNTIF(C5:C15,">3/1/2021")-COUNTIF(C5:C15,">3/30/2021")Here, C5:C15 is the date range, “>3/1/2021”, and “>3/30/2021” are the multiple criteria.
Formula Breakdown
- COUNTIF(C5:C15,”>3/1/2021″) → counts the number of dates greater than the start date of March
- Output → 4
- COUNTIF(C5:C15,”>3/30/2021″) → counts the number of dates greater than the ending date of March
- Output → 2
- COUNTIF(C5:C15,”>3/1/2021″)-COUNTIF(C5:C15,”>3/30/2021″) → becomes
- 4-2 → 2
- Press ENTER.
In this way, you will have the total number of dates from March month and here the result is 2.
Read More: How to Use COUNTIF with Multiple Criteria in the Same Column in Excel
Example-4: Using DATE Function with COUNTIF Function in Excel
Here, we will try to count the number of dates from the month of March presented in the Date column using the COUNTIF and DATE functions.
- Type the following formula in cell E8.
=COUNTIF(C5:C15,">"&DATE(2021,3,1))-COUNTIF(C5:C15,">"&DATE(2021,3,30))Here, C5:C15 is the date range.
Formula Breakdown
- DATE(2021,3,1) → returns date with the year 2021, month 3, and 1st day of this month.
- Output → 3/1/2021
- COUNTIF(C5:C15,”>”&DATE(2021,3,1)) → becomes
- COUNTIF(C5:C15,”>”&3/1/2021) → The & operator joins the greater than sign with the date.
- COUNTIF(C5:C15,”>3/1/2021″) → counts the number of dates greater than the start date of March
- Output → 4
- DATE(2021,3,30) → returns date with the year 2021, month 3, and 30th day of this month.
- Output → 3/30/2021
- COUNTIF(C5:C15,”>”&DATE(2021,3,30)) → becomes
- COUNTIF(C5:C15,”>”&3/30/2021) → The & operator joins the greater than sign with the date.
- COUNTIF(C5:C15,”>3/30/2021″) → counts the number of dates greater than the start date of March
- Output → 2
- COUNTIF(C5:C15,”>”&DATE(2021,3,1))-COUNTIF(C5:C15,”>”&DATE(2021,3,30)) → becomes
- 4-2 → 2
- Press ENTER.
Later, you will have the total number of dates from March month and here the result is 2.
Read More: COUNTIF Multiple Ranges Same Criteria in Excel
Example-5: Applying EOMONTH and COUNTIF Functions with Multiple Criteria in a Date Range
Here, we will try to count the number of dates from the month of March presented in the Date column using the COUNTIF and EOMONTH functions.
- Type the following formula in cell E8.
=COUNTIF(C5:C15,">"&E6)-COUNTIF(C5:C15,">"&EOMONTH(E6,0))Here, C5:C15 is the date range, and E6 is the starting date of March.
Formula Breakdown
- COUNTIF(C5:C15,”>”&E6) → becomes
- COUNTIF(C5:C15,”>”&3/1/2021) → The & operator joins the greater than sign with the date.
- COUNTIF(C5:C15,”>3/1/2021″) → counts the number of dates greater than the start date of March
- Output → 4
- EOMONTH(E6,0) returns the end date of March
- Output → 3/31/2021
- COUNTIF(C5:C15,”>”&EOMONTH(E6,0)) → becomes
- COUNTIF(C5:C15,”>”&3/31/2021) → The & operator joins the greater than sign with the date.
- COUNTIF(C5:C15,”>3/31/2021″) → counts the number of dates greater than the start date of March
- Output → 2
- COUNTIF(C5:C15,”>”&DATE(2021,3,1))-COUNTIF(C5:C15,”>”&DATE(2021,3,31)) → becomes
- 4-2 → 2
- After pressing ENTER, we are getting the total number of dates from March in the Date column.
Read More: COUNTIF with Multiple Criteria in Different Columns in Excel (Both Single and Multiple Criteria)
Example-6: Implementing COUNT, IF, MONTH, and YEAR Functions
Here, we will count the number of dates from January month and the dates from this month when the sales values were greater than 5000 USD. For this purpose, we are going to use the combination of the COUNT, IF, MONTH, and YEAR functions.
- Apply the following formula in cell E8.
=COUNT(IF(MONTH(C5:C15)=1,IF(YEAR(C5:C15)=2021,IF(D5:D15>5000,D5:D15))))Here, C5:C15 is the date range.
Formula Breakdown
- MONTH(C5:C15)=1 → MONTH returns the month values of the dates
- {1;1;1;2;2;3;3;1;4;4;2} = 1 → returns TRUE for 1 in the array
- Output → {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
- {1;1;1;2;2;3;3;1;4;4;2} = 1 → returns TRUE for 1 in the array
- YEAR(C5:C15)=2021 → YEAR returns the year values of the dates
- {2021;2021;2021;2021;2021;2021;2021;2021;2021;2021;2021} = 2021 → returns TRUE for 2021 in the array
- Output → {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
- {2021;2021;2021;2021;2021;2021;2021;2021;2021;2021;2021} = 2021 → returns TRUE for 2021 in the array
- D5:D15>5000 → returns TRUE when the sales values are greater than 5000
- Output → {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
- IF(D5:D15>5000,D5:D15) → becomes
- IF({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},D5:D15)
- Output → {FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
- IF({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},D5:D15)
- IF(YEAR(C5:C15)=2021,IF(D5:D15>5000,D5:D15)) → becomes
- IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})
- Output → {FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
- IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE},{FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})
- IF(MONTH(C5:C15)=1,IF(YEAR(C5:C15)=2021,IF(D5:D15>5000,D5:D15))) → becomes
- IF({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})
- Output → {FALSE;5935;6835;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
- IF({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{FALSE;5935;6835;7871;9496;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})
- COUNT(IF(MONTH(C5:C15)=1,IF(YEAR(C5:C15)=2021,IF(D5:D15>5000,D5:D15)))) → becomes
- COUNT({FALSE;5935;6835;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}) → counts the number values in the range
- Output → 2
- COUNT({FALSE;5935;6835;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}) → counts the number values in the range
- Press ENTER.
In this way, you will get the total number 2 for the sales values greater than 5000 USD during January month.
Note: You have to press CTRL+SHIFT+ENTER instead of pressing ENTER while using any other versions except for Microsoft Excel 365.
Applying COUNTIFS Function for Multiple Criteria in a Date Range in Excel
Here, we are going to use the COUNTIFS function for counting values based on multiple criteria of the Date range and the Sales range. So, we will count the number of dates from January month and the dates from this month when the sales values were greater than 5000 USD.
- Type the following formula in cell E8.
=COUNTIFS(C5:C15,">1/1/2021",C5:C15,"<1/31/2021",D5:D15,">"&5000)Here, C5:C15, and D5:D15 are the criteria ranges, “>1/1/2021”, “<1/31/2021”, and “>”&5000 are the criteria.
- Press ENTER.
Eventually, you will get the total number 2 for the sales values greater than 5000 USD during January month.
Read More: Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function
Practice Section
For doing practice, we have added a Practice portion on each sheet on the right portion.
Conclusion
In this article, we tried to show some examples related to using the COUNTIF function with multiple criteria date range in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
