Date & Time
Count Dates & Sum Amounts in a Given Calendar Year (COUNTIFS & DATE)
Count transactions and sum revenue occurring in any target year Y using blank-safe Jan 1 to Dec 31 DATE boundaries inside COUNTIFS and SUMIFS.
Why Jan 1 – Dec 31 DATE boundaries beat YEAR() array wrappers!
When counting how many invoices fell in 2026, wrapping YEAR() around a column with blank rows carries a classic trap: an empty cell evaluates as serial 0 (1900-01-00), and YEAR(0) returns 1900—or errors out if text headers slip in! Bounding the date column directly with COUNTIFS(A2:A8, ">=" & DATE(E2, 1, 1), A2:A8, "<=" & DATE(E2, 12, 31)) ignores blank cells and text notes automatically, handles leap years (like Feb 29) natively, and scales fast over tens of thousands of rows!
The exact same two boundary criteria drop straight into SUMIFS(B2:B8, A2:A8, ">=" & DATE(E2, 1, 1), A2:A8, "<=" & DATE(E2, 12, 31)) to total annual revenue for year E2!
| Target Year (E2) | Start Boundary DATE(E2,1,1) | End Boundary DATE(E2,12,31) | Matching Count & Annual Sum |
|---|---|---|---|
| 2026 | 2026-01-01 (inclusive) | 2026-12-31 (inclusive) | 4 invoices ($18,400) |
| 2025 | 2025-01-01 (inclusive) | 2025-12-31 (inclusive) | 2 invoices ($7,250) |
| 2028 (No Activity) | 2028-01-01 | 2028-12-31 | 0 invoices ($0) |
Count Dates Occurring in Target Year Y
Counts dates in A2:A8 between January 1 and December 31 (inclusive) of the 4-digit year in E2.
=COUNTIFS(A2:A8, ">=" & DATE(E2, 1, 1), A2:A8, "<=" & DATE(E2, 12, 31))Sum Amounts Occurring in Target Year Y
Totals invoice amounts in B2:B8 falling inside calendar year E2.
=SUMIFS(B2:B8, A2:A8, ">=" & DATE(E2, 1, 1), A2:A8, "<=" & DATE(E2, 12, 31))Try the example
Use the original values or change them to test your understanding.