Excel COUNTIF Function with Multiple Criteria & Date Range

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.

countif function in excel with multiple criteria date range

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.

using countif function with multiple criteria date range in Excel

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

result

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.

applying sumproduct and countif functions with multiple criteria date range in excel

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

counting number between two dates using countif function with multiple criteria date range

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

result

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.

using DATE function with countif function with multiple criteria date range in Excel

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

Applying EOMONTH Function and COUNTIF Function with Multiple Criteria in a Date Range

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

combination of 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}
  • 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}
  • 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(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(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}
  • 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

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

Applying COUNTIFS Function instead of Excel COUNTIF function for Multiple Criteria in a Date Range

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

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo