Function Problems

How to Use COUNTIFS to Count Dates Between Two Values in Excel

Adam Davis Sep 29, 2026 868 views

Question details

The user needs to count the number of cells containing dates that fall between a specific start date and end date using the COUNTIFS function.

How to Use COUNTIFS to Count Dates Between Two Values in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Counting records or occurrences that happen within a dynamically defined timeframe using a start and end date reference.
Observed behavior: The user requires a reliable formula to evaluate dates within a range, avoiding incorrect methods like nesting an AND function inside a single COUNTIF.

Before you start

Ensure that the data range you want to count and the reference cells for your start and end dates are properly formatted as valid dates, not as text.

Solution 1Recommended

Use COUNTIFS with Start and End Date Criteria

This method utilizes the COUNTIFS function to evaluate two conditions (greater than or equal to the start date, and less than or equal to the end date) across the same date range.

Instead of using a complex nested AND function inside COUNTIF, the COUNTIFS function natively supports evaluating multiple criteria simultaneously.

By applying two different logical tests to the same range, you can quickly filter and count occurrences that happen within a specific timeframe.

Format cells as dates

Verify that your data range (e.g., Log!$C$2:$C$83) and your condition cells (e.g., AR2 and AG2) contain valid dates and not text strings.

Input the formula

Select the cell for your result and type `=COUNTIFS(Log!$C$2:$C$83, ">="&AR2, Log!$C$2:$C$83, "<="&AG2)`.

Check date logic

Ensure that the start date in AR2 is chronologically before or equal to the end date in AG2, otherwise, the formula will return 0.

Calculate the result

Press Enter to execute the formula and view the total number of dates that fall within your specified range.

Use COUNTIFS with Start and End Date Criteria

Using the Ampersand: When referencing cells for your criteria, you must use the ampersand (&) operator to concatenate the comparison symbols (>= or <=) with the cell reference.

Data Analysis Tools

Perform Advanced Date Calculations with WPS Spreadsheet

WPS Spreadsheet perfectly supports complex formulas like COUNTIFS, allowing you to seamlessly analyze date ranges and track timelines. It is a powerful and free tool for all your data management needs.

1. Open WPS Spreadsheet: Launch WPS Office and open your data file.
2. Set up your criteria: Ensure your start and end dates are placed in designated reference cells.
3. Apply COUNTIFS: Type the formula using the exact same syntax as Excel to instantly get your result.

100% compatible with Microsoft Excel formulas and file formatsFree, lightweight, and fast-loading spreadsheet toolIntuitive interface for tracking timelines and analyzing dataBuilt-in robust date and time formatting features

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula returning zero?

This usually happens if the dates are formatted as text instead of numerical dates, or if the start date in your criteria is later than the end date.

Can I hardcode the dates directly into the COUNTIFS formula?

Yes. Instead of cell references, you can type dates directly into the formula criteria like this: `=COUNTIFS(C2:C83, ">=1/1/2023", C2:C83, "<=12/31/2023")`.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF evaluates a single condition on a single range, whereas COUNTIFS allows you to apply multiple criteria to different or the same ranges simultaneously, making it ideal for checking data between two boundaries.

How do I exclude the start and end dates from the count?

To count dates strictly between two values without including the start and end dates themselves, use strictly greater than (>) and less than (<) operators instead of greater than or equal to (>=) and less than or equal to (<=).

Excel & Data Problems