Why COUNTIFS with dates returns the wrong count in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))Usually some "dates" are text, which COUNTIFS skips. Convert them inside the formula: =SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2)), or fix the column.
- Result on the example
- 7 in
G3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))Result7
B2:B1110 rows × 1 col: 2/27/2026, "3/2/2026", 3/5/2026, "3/9/2026", …A range: 10 cells in one column.
-B2:B11{10 rows × 1 col} -46080, -46083, -46086, -46090, …Makes the value negative.
--B2:B11{10 rows × 1 col} 46080, 46083, 46086, 46090, …Makes the value negative.
G13/1/2026The value in G1.
--B2:B11>=G1{10 rows × 1 col} FALSE, TRUE, TRUE, TRUE, …The >= operator tests at least.
-B2:B11{10 rows × 1 col} -46080, -46083, -46086, -46090, …Makes the value negative.
--B2:B11{10 rows × 1 col} 46080, 46083, 46086, 46090, …Makes the value negative.
G23/31/2026The value in G2.
--B2:B11<=G2{10 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <= operator tests at most.
--B2:B11>=G1)*(--B2:B11<=G2{10 rows × 1 col} 0, 1, 1, 1, …The * operator multiplies.
SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2))7SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--B2:B11>=G1)*(--B2:B11<=G2{10 rows × 1 col} 0, 1, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((--B2:B11>=G1)*(--B2:B11<=G2)). We ran it in Google Sheets mode on the same data and it returns 7.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIFS(B2:B11,">="&G1,B2:B11,"<="&G2) | 4Different result | 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:
"<="&G2misses anything after midnight on the last day. Use"<"&G2+1.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why COUNTIFS with dates returns the wrong count
G3, count the orders dated from G1 to G2, including the ones whose date was imported as text.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.