# Why COUNTIFS with dates returns the wrong count in Excel

Usually some "dates" are text, which COUNTIFS skips. Convert them inside the formula: `=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))`, or fix the column.

**Excel:** `=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | Date (imported) |  |  |  | From | 2026-03-01 |
| R-01 | 2026-02-27 |  |  |  | To | 2026-03-31 |
| R-02 | 3/2/2026 |  |  |  | Orders |  |
| R-03 | 2026-03-05 |  |  |  |  |  |
| R-04 | 3/9/2026 |  |  |  |  |  |
| R-05 | 2026-03-15 |  |  |  |  |  |
| R-06 | 3/21/2026 |  |  |  |  |  |
| R-07 | 2026-03-31 |  |  |  |  |  |
| R-08 | 4/1/2026 |  |  |  |  |  |
| R-09 | 2026-03-18 |  |  |  |  |  |
| R-10 | 2/28/2026 |  |  |  |  |  |

Formula in G3: `=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))`

Result: 7

## How it works

Half of these dates came in as text: they sit on the left of the cell and COUNTIFS ignores them, so `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)` finds only 4 orders. The double minus converts each value to a number first: real dates stay as they are, and text like "3/9/2026" becomes the date it describes. Then the comparison counts all 7 March orders.

## Other ways

- `=COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2)`: The formula that seems wrong: it is right for real dates but skips text. (Returns 4: the three March dates stored as text are skipped.)

## Common mistakes

- Text dates only convert if they match your system's date format. "14/03/2026" is not a date in a US setup, and the formula returns #VALUE!.
- The lasting fix is to convert the column: select it, then Data, Text to Columns, Finish (choose the date order in step 3), or use `=DATEVALUE(B2)` in a helper column.
- Dates with times also cause low counts: `"<="&G2` misses anything after midnight on the last day. Use `"<"&G2+1`.

## Questions

**How can I tell which dates are text?**

Use `=ISTEXT(B2)` next to each, or `=SUMPRODUCT(--ISTEXT(B2:B11))` to count them.

**Why does my date criteria typed in the formula not work?**

Keep dates in cells and join them: `">="&G1`. A date typed inside quotes depends on your regional format.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/countif-dates-not-working