Date & Time
Count Birthdays & Records by Year Cohort (Avoiding the Blank-Cell 1900 Trap)
Count birthdates or hire dates belonging to a specific calendar year or multi-year cohort using COUNTIFS date bounds and blank-guarded SUMPRODUCT + YEAR.
Why unguarded YEAR(B2:B8) = 1900 miscounts empty cells
In 1900-epoch spreadsheets, an empty cell coerced into a date serial evaluates to 0 (1900-01-00), so YEAR(blank_cell) returns 1900. While a query for 1994 looks harmless, auditing historical cohorts or comparing YEAR(B2:B8) <= cutoff silently sweeps every blank row into the 1900 bucket.
You can eliminate that hazard completely in two ways: bound the target year with COUNTIFS(B2:B8, ">=" & DATE(yr, 1, 1), B2:B8, "<=" & DATE(yr, 12, 31))—which ignores blanks natively—or gate array-level YEAR extraction with --NOT(ISBLANK(B2:B8)) inside SUMPRODUCT.
| Approach | Formula | Handles Blanks Safely? | Supports Multi-Year Span? |
|---|---|---|---|
| COUNTIFS Calendar Window | COUNTIFS($B$2:$B$8, ">=" & DATE(D2,1,1), $B$2:$B$8, "<=" & DATE(D2,12,31)) | Yes (ignores blanks) | Yes (change end year) |
| Blank-Guarded SUMPRODUCT | SUMPRODUCT(--NOT(ISBLANK($B$2:$B$8)), --(YEAR($B$2:$B$8) = D2)) | Yes (explicitly masked) | Yes (>= start, <= end) |
| Unguarded YEAR Array | SUMPRODUCT(--(YEAR($B$2:$B$8) = D2)) | Fails if D2=1900 or <= cutoff | Risky on padded ranges |
Exact Single-Year Cohort Count with COUNTIFS
Construct January 1 and December 31 date serials from the target year in D2 so leap years (such as 1996-02-29) and year-end dates are captured inclusively.
=COUNTIFS($B$2:$B$8, ">=" & DATE(D2, 1, 1), $B$2:$B$8, "<=" & DATE(D2, 12, 31))Multi-Year Generation Cohort Count with COUNTIFS
Span any multi-year generation window [G2, H2] via DATE(G2, 1, 1) and DATE(H2, 12, 31) so blank cells and text placeholders ("TBD") are ignored automatically without #VALUE! or 1900 coercion.
=COUNTIFS($B$2:$B$8, ">=" & DATE(G2, 1, 1), $B$2:$B$8, "<=" & DATE(H2, 12, 31))Try the example
Use the original values or change them to test your understanding.