Count Excel Formulas

Count cells between two dates in Excel

CA Anshuman Tandon

When you work with date data in Excel, a very common task is: “How many records fall between these two dates?” Maybe they’re orders, attendance days, or transactions. Being able to count cells between dates quickly turns a messy table into a clean summary you can actually use for decisions.

In this guide, you’ll see how to count cells between two dates using COUNTIFS and SUMPRODUCT, and then how to sum amounts in that same date range. The examples are beginner‑friendly but accurate enough for real‑life reports.

What it means to “count cells between dates”

Before jumping into formulas, it helps to clarify what we’re actually doing.

  • You have a column containing valid Excel dates (not text).
  • You have a start date and an end date somewhere on your sheet (for example in G4 and G5).
  • You want to know how many rows have dates that are greater than or equal to the start date and less than or equal to the end date.

So logically, the condition is:

  • date >= start_date
  • date <= end_date

Both conditions must be true for a row to be counted.

Using COUNTIFS to count dates between two dates

COUNTIFS is designed to count cells that meet multiple conditions, which makes it perfect for “between two dates” logic.

Example setup

Imagine this layout:

  • Dates are in D5:D16.
  • You’ve created a named range date that refers to D5:D16.
  • Cell G4 contains the start date (e.g., 01‑Jun‑2022).
  • Cell G5 contains the end date (e.g., 15‑Jun‑2022).

The core formula to count dates between these two values is:

text=COUNTIFS(date, ">=" & G4, date, "<=" & G5)

How it works:

  • date, ">=" & G4 means “count only rows where the date is on or after the start date in G4”.
  • date, "<=" & G5 means “and also on or before the end date in G5”.

Because COUNTIFS uses AND logic, a date must satisfy both conditions to be included in the final count.

Why the >= and <= are in quotes

You may notice the slightly strange syntax: ">=" & G4 instead of just >=G4.

  • The comparison operator like ">=" must be a text string, so it goes inside double quotes.
  • Then it’s concatenated (joined) with the cell reference using &.

So " >= " & G4 becomes something like ">=44722" internally, where 44722 is the serial number representing a date in Excel. This is a pattern you’ll use for pretty much all COUNTIFS date criteria.

Alternative method: SUMPRODUCT for date ranges

If you prefer a more “math‑like” formula, or you run into COUNTIFS limitations, SUMPRODUCT is a very flexible alternative. It lets you write the conditions more directly, without concatenating operator strings.

With the same named range date (D5:D16), and start/end dates in G4 and G5, you can write:

text=SUMPRODUCT((date >= G4) * (date <= G5))

What’s happening here:

  • (date >= G4) returns an array of TRUE/FALSE for each row.
  • (date <= G5) returns another array of TRUE/FALSE.
  • Multiplying the arrays coerces them into 1s and 0s:
    • TRUE becomes 1
    • FALSE becomes 0
    • 1 * 1 = 1 (row meets both conditions)
    • anything * 0 = 0 (row fails at least one condition)

After multiplication, you get an array of 1s and 0s representing which rows are inside the date range. SUMPRODUCT simply adds those 1s to return the final count.

This might sound a bit abstract, but it’s just Boolean logic written in a numeric way.

Practical example: counting June orders

Let’s walk through a simple case you might see in a small business or project tracker.

  • Column C: Amount (sales values).
  • Column D: OrderDate (dates).
  • You name C5:C16 as amount and D5:D16 as date.
  • G4: 01‑06‑2022 (start date).
  • G5: 15‑06‑2022 (end date).

You want:

  1. The number of orders between these dates.
  2. The total sales amount for orders in that period.

You can do:

Count the orders:

text=COUNTIFS(date, ">=" & G4, date, "<=" & G5)

or, using SUMPRODUCT:

text=SUMPRODUCT((date >= G4) * (date <= G5))

Both will give you the same count.

Summing amounts between two dates (SUMIFS and SUMPRODUCT)

Usually, after counting records, the next question is: “OK, now what’s the total amount for those dates?”

You can use SUMIFS or extend the SUMPRODUCT formula.

Using SUMIFS

SUMIFS is like COUNTIFS but returns a sum instead of a count.

With the named ranges:

  • amount = C5:C16
  • date = D5:D16

And start/end dates in G4:G5, you can write:

text=SUMIFS(amount, date, ">=" & G4, date, "<=" & G5)

This sums all values in amount where the corresponding date is between G4 and G5 (inclusive). It uses the same criteria structure as COUNTIFS.

Using SUMPRODUCT to sum

If you already like the SUMPRODUCT style, you can do:

text=SUMPRODUCT((date >= G4) * (date <= G5) * amount)

Here:

  • (date >= G4) * (date <= G5) gives 1 for rows inside the range, 0 otherwise.
  • Multiplying by amount keeps the value for qualifying rows and zeroes out non‑qualifying rows.
  • SUMPRODUCT then adds everything and returns the total amount for the date range.

This pattern is very powerful because you can add more conditions by multiplying more logical expressions.

Tips, best practices, and common mistakes

A few small things can make or break these formulas.

1. Make sure your dates are real dates

Excel stores dates as serial numbers, and all these comparisons work on those numbers. If your “dates” are text, the formulas may return wrong counts or zeros.

Quick checks:

  • Try changing the cell format to Number; a valid date will show a big integer (like 44722).
  • If it doesn’t change or shows something weird, you may need to convert text to dates first.

2. Put your criteria dates in cells, not hard‑coded

It’s tempting to write something like:

text=COUNTIFS(date, ">=" & DATE(2022,6,1), date, "<=" & DATE(2022,6,15))

This works, but in real life, it’s usually better to:

  • Put the start date in G4, end date in G5.
  • Reference them in formulas as G4 and G5.

That way, if your reporting period changes, you just edit the dates in the cells instead of opening and editing formulas everywhere.

3. Watch inclusive vs exclusive ranges

Using >= and <= means both endpoints are included.

  • If you want a range like “on or after 1‑Jun and before 15‑Jun”, you would use >= for the start and < for the end:text=COUNTIFS(date, ">=" & G4, date, "<" & G5)

Make sure this matches your reporting logic so you don’t double‑count when chaining periods.

4. Choose COUNTIFS or SUMPRODUCT based on your comfort

  • COUNTIFS / SUMIFS are very readable and ideal when you like the range/criteria pattern.
  • SUMPRODUCT is more compact and flexible, especially if you later want to layer in extra numeric logic, but it can look a bit scary at first.

Both are valid; pick the one that you and your team can understand a few months later.

Key takeaways and what to try next

Counting cells between two dates in Excel is really about:

  • Applying two conditions: one for the start date, one for the end date.
  • Using either COUNTIFS (with range/criteria pairs) or SUMPRODUCT (with Boolean expressions).
  • Extending the same logic to sum amounts with SUMIFS or SUMPRODUCT.

Tags:

Related tutorials