# Using the Excel COUNTIF Function with Multiple Criteria and a Date Range – 6 Examples

This is the sample dataset.

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

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

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

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria

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

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

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

• 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

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

• 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

• Press ENTER.

This is the output.

## Applying the COUNTIFS Function for Multiple Criteria in a Date Range in Excel

To count the number of dates in January with sales values greater than 5000 USD.

• Enter the following formula in E8.
`=COUNTIFS(C5:C15,">1/1/2021",C5:C15,"<1/31/2021",D5:D15,">"&5000)`

C5:C15, and D5:D15 are the criteria ranges, “>1/1/2021”, “<1/31/2021”, and “>”&5000 are the criteria.

• Press ENTER.

This is the output.

## Practice Section

Practice here.

Download Workbook

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF