Excel COUNTIF and SUMIF With Dates: Practical Examples

Formulas & functions · Updated November 19, 2026

To count or sum rows by date in Excel, use COUNTIF or SUMIF with a date criterion; for a period such as a month, use COUNTIFS or SUMIFS with a start date and an exclusive end date. Build criteria with DATE or a cell reference so Excel compares actual dates reliably, rather than relying on how a date is displayed.

How Excel date criteria work

Excel stores a real date as a number, and a date-and-time value as a number with a fractional part. Cell formatting controls how that value looks—such as 2/1/2024 or Feb 1—but does not change the underlying value. COUNTIF and SUMIF compare that stored value against their criteria.

COUNTIF takes a range to examine and one criterion. SUMIF takes a criteria range, one criterion, and the range to add. Both can test for an exact date or for dates before or after a boundary. When you need two boundaries, one for the beginning and one for the end of a period, the plural functions COUNTIFS and SUMIFS are the natural choices.

For examples, assume a worksheet has dates in A2:A9 and related amounts in B2:B9:

  • Jan 5, 2024 — 120
  • Jan 15, 2024 — 85
  • Feb 1, 2024 — 200
  • Feb 14, 2024 — 50
  • Feb 29, 2024 — 40
  • Mar 1, 2024 — 175
  • Mar 20, 2024 — 90
  • Mar 31, 2024 — 60

These should be real Excel dates, not text that merely looks like dates. The amounts total 820, which provides a useful check on date-based sums.

Count records for one exact date

To count records on one particular day, compare the date range with a date value. With the dates in A2:A9 and the example records above, use this formula to count Feb 29, 2024:

=COUNTIF(A2:A9,DATE(2024,2,29))

The result is 1 because one row has that date. DATE takes year, month, and day as separate numeric arguments, so the formula does not depend on whether your Excel settings display dates as month/day/year or day/month/year. For a date already in a cell, use that cell as the criterion instead; the cell must contain a real date value.

You can also count dates before or after a boundary. With dates in A2:A9, use this formula to count dates strictly later than Feb 14, 2024:

=COUNTIF(A2:A9,">"&DATE(2024,2,14))

The result is 4: Feb 29, Mar 1, Mar 20, and Mar 31. The operator is written as text inside quotation marks, then joined to the date value with the ampersand. Use >= or <= when the boundary day should be included. An exact-date criterion is not the same as “any time during that calendar day” if the cells contain times; see the timestamp section below.

Count dates in a month or other period

A month has both a first day and a last day, so a robust test uses two criteria: greater than or equal to the first day, and less than the first day of the next period. This avoids having to calculate the last day of each month and handles February and leap years automatically.

With dates in A2:A9, count the records in February 2024 like this:

=COUNTIFS(A2:A9,">="&DATE(2024,2,1),A2:A9,"<"&DATE(2024,3,1))

The result is 3: Feb 1, Feb 14, and Feb 29. The first boundary includes Feb 1; the second excludes Mar 1. For January, use Jan 1 as the lower boundary and Feb 1 as the upper boundary. For a year, use Jan 1 of that year and Jan 1 of the following year.

This half-open interval—include the start, exclude the next period’s start—is useful beyond months. It works for weeks, quarters, and custom reporting periods too. It also avoids the common mistake of setting an upper boundary to the last date at midnight, which can miss records later on that date if times are present.

Sum amounts for dates with SUMIF and SUMIFS

SUMIF is appropriate when one date condition is enough. With dates in A2:A9 and amounts in B2:B9, sum the amounts on or after Mar 1, 2024:

=SUMIF(A2:A9,">="&DATE(2024,3,1),B2:B9)

The result is 325: 175 + 90 + 60. The first range is checked against the criterion, and the third argument identifies which values to add. Keep the date range and amount range aligned row by row; if the ranges start or end on different rows, the result may not represent the intended records.

For a month, use SUMIFS, which accepts multiple conditions. Its argument order differs from SUMIF: SUMIFS starts with the sum range, followed by a criteria range and criterion for each condition. With dates in A2:A9 and amounts in B2:B9, sum February 2024:

=SUMIFS(B2:B9,A2:A9,">="&DATE(2024,2,1),A2:A9,"<"&DATE(2024,3,1))

The result is 290: 200 + 50 + 40. This is a frequent source of errors: SUMIF puts its criteria range first, while SUMIFS puts its sum range first. COUNTIFS and SUMIFS also let you add other conditions, such as a salesperson or status, without creating helper columns.

Use cell references for flexible date criteria

Hard-coded dates are convenient for a one-off calculation, but date cells make a report easier to reuse. For example, put the start date in D1 and the end date in E1, with dates still in A2:A9. To count dates from D1 through E1 inclusive, enter:

=COUNTIFS(A2:A9,">="&D1,A2:A9,"<"&(E1+1))

If D1 is Feb 1, 2024 and E1 is Feb 29, 2024, the result is 3. Adding 1 to the end date changes an inclusive date-only end boundary into an exclusive boundary at the next midnight. For data that may contain times, this includes every time on E1 as well. Make sure D1 and E1 contain actual dates, and check that the start is not later than the end.

A cell reference can also follow an operator. With dates in A2:A9 and a real date in D1, count dates later than D1 using:

=COUNTIF(A2:A9,">"&D1)

If D1 is Feb 14, 2024, the result is 4. The ampersand combines the comparison operator with the value in D1; without that construction, Excel may interpret the criterion differently than intended.

Dates that include times

A timestamp includes a time fraction, so a cell showing Feb 1, 2024 may actually hold Feb 1 at 12:30 p.m. An exact-date criterion built from DATE represents midnight. Consequently, an exact COUNTIF or SUMIF match can miss timestamps later that day, even though the cells display the same calendar date.

For a clear example, suppose A2:A4 contain Feb 1 at midnight, Feb 1 at 12:30 p.m., and Feb 2 at midnight, while B2:B4 contain 10, 20, and 30. To count all entries on Feb 1, use:

=COUNTIFS(A2:A4,">="&DATE(2024,2,1),A2:A4,"<"&DATE(2024,2,2))

The result is 2. With timestamps in A2:A4 and amounts in B2:B4, sum those same Feb 1 entries with:

=SUMIFS(B2:B4,A2:A4,">="&DATE(2024,2,1),A2:A4,"<"&DATE(2024,2,2))

The result is 30. The next-day exclusive limit includes both Feb 1 times but not midnight at the start of Feb 2. This pattern is generally safer for logs, exports, and transaction data than attempting to match a date exactly.

Common date-criteria problems and fixes

  • The criteria use a typed date string. A criterion such as a quoted date may be interpreted according to regional settings. Prefer DATE(year,month,day), or refer to a cell containing a genuine date.
  • Dates are stored as text. Imported values can look like dates but fail numeric date comparisons. Changing the number format alone does not convert text into date values. Convert the source data to real dates before relying on date criteria.
  • The range contains hidden times. Formatting a timestamp to show only a date hides the time but does not remove it. Use a start-inclusive, next-period-exclusive pair of criteria to include a whole day.
  • The boundary is off by one day. Decide explicitly whether the start and end are included. For whole-day periods, include the start and exclude the next day after the intended end.
  • Ranges do not match. In SUMIF, the criteria range and sum range should cover corresponding rows. In SUMIFS, each criteria range should have the same dimensions as the sum range.
  • The formula returns zero unexpectedly. Check for text dates, blank or misspelled criteria-cell values, and timestamps. Also verify the actual stored values by temporarily applying a date-and-time number format or inspecting the source data.

When copying a formula, use fixed references such as $A$2:$A$9 if the data range should not shift. If the source is an Excel Table, structured references can make formulas expand as rows are added, but the same principle still applies: the date criteria must compare against real date values.

Choosing between COUNTIF, COUNTIFS, SUMIF, and SUMIFS

Use COUNTIF to count records meeting one condition, such as an exact date or dates after a cutoff. Use SUMIF to add values for one condition. Choose COUNTIFS or SUMIFS when you need a date range or multiple filters. For example, “February dates and status is Paid” requires two conditions, so a plural function is appropriate.

These functions are useful for fixed, clearly bounded criteria and ordinary summary reports. For a more interactive report, a PivotTable can group date fields and summarize counts or amounts; however, grouping depends on the source values being valid dates and may require refreshing the PivotTable after data changes. The formulas here remain useful when a result must appear in a particular cell or update directly from chosen date boundaries.

Frequently Asked Questions

Can COUNTIF count dates in a month?
COUNTIF accepts only one criterion, so it cannot express both the first day and the next month’s first day by itself. Use COUNTIFS with those two boundaries; the same approach works for quarters and years.

Why does SUMIF return zero for dates that look correct?
The date values may be stored as text, or the criterion may be a text string interpreted differently under your regional settings. Confirm that the source contains real Excel dates and construct criteria with DATE or a cell containing a real date.

Can I count dates including the start and end days?
Yes. Include the start with a greater-than-or-equal condition and make the upper limit less than the day after the requested end. This reliably includes the entire end date, even when timestamps are present.

Will an exact-date formula match a timestamp?
Only if the timestamp is exactly midnight. A time adds a fractional part to the stored date, so use two boundaries covering the day when you need all times on that date.

Does changing a cell’s date format fix text dates?
No. Formatting changes how a value is displayed; it does not necessarily convert text into a numeric date. Convert the data itself, then apply an appropriate date format if needed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.