Using the Excel COUNTIF Function with Multiple Criteria & Date Range

This is the sample dataset.

countif function in excel with multiple criteria date range

Example 1 – Counting the Number of Different Dates Using the COUNTIF Function with a Multiple Criteria Date Range

To count 1/9/2021 and 2/25/2021 (mm/dd/yyyy format) among the dates in the Date range:

Enter the following formula in E8.

=COUNTIF(C5:C15,"1/9/2021")+COUNTIF(C5:C15,"2/25/2021")

C5:C15 is the date range, and “1/9/2021”, and “2/25/2021” are the criteria.

Formula Breakdown

COUNTIF(C5:C15,”1/9/2021″) → returns the number of times the date 1/9/2021 is present in the range.
- Output → 2
COUNTIF(C5:C15,”2/25/2021″) → returns the number of times the date 2/25/2021 is present in the range.
- Output → 2
COUNTIF(C5:C15,”1/9/2021″)+COUNTIF(C5:C15,”2/25/2021″) → becomes
- 2+2 → 4

Press ENTER.

4 is the output.

result

Example 2 -Applying the SUMPRODUCT & COUNTIF Functions to Multiple Criteria in a Date Range

To get the output of Example 1, combine the SUMPRODUCT and the COUNTIF functions.

Use the following formula in E8.

=SUMPRODUCT(COUNTIF(C5:C15,{"1/9/2021","2/25/2021"}))

C5:C15 is the date range, and “1/9/2021”, and “2/25/2021” are the criteria.

Formula Breakdown

COUNTIF(C5:C15,{“1/9/2021″,”2/25/2021”}) → returns an array containing the number “1/9/2021”, and “2/25/2021” appear in the date range.
- Output → {2,2}
SUMPRODUCT({2,2}) → adds the values inside the array
- Output → 4

Press ENTER.

4 is the output.

Note: Press CTRL+SHIFT+ENTER instead of ENTER in versions other than Microsoft Excel 365.

Example 3 – Counting Numbers Between Two Dates

To count the number of dates in March in the Date column.

Use the following formula in E8.

=COUNTIF(C5:C15,">3/1/2021")-COUNTIF(C5:C15,">3/30/2021")

C5:C15 is the date range, “>3/1/2021”, and “>3/30/2021” are the 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.

The output is 2.

result

Example 4 – Using the DATE Function with the COUNTIF Function in Excel

To count the number of dates from in March in the Date column using the COUNTIF and DATE functions:

Enter the following formula in E8.

=COUNTIF(C5:C15,">"&DATE(2021,3,1))-COUNTIF(C5:C15,">"&DATE(2021,3,30))

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.

The output is 2.

Similar Readings

Example 5 – Applying the EOMONTH and the COUNTIF Functions with Multiple Criteria in a Date Range

To count the number of dates in March in the Date column using the COUNTIF and EOMONTH functions:

Use the following formula in E8.

=COUNTIF(C5:C15,">"&E6)-COUNTIF(C5:C15,">"&EOMONTH(E6,0))

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

Press ENTER to see the output.

Example 6 – Using the COUNT, IF, MONTH, and YEAR Functions

To count the number of dates in January with sales values greater than 5000 USD, using the COUNT, IF, MONTH, and YEAR functions:

Use the following formula in E8.

=COUNT(IF(MONTH(C5:C15)=1,IF(YEAR(C5:C15)=2021,IF(D5:D15>5000,D5:D15))))

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