Math
Sum and count payments by calendar year, including December 31 timestamps
Use an inclusive January 1 start and exclusive next-January-1 end to include every timestamp in the selected year.
Use a half-open date interval
Compare the date range directly with >= DATE(year,1,1) and < DATE(year+1,1,1). Using <= DATE(year,12,31) would stop at midnight at the start of December 31 and omit payments later that day.
The workbook uses DATE formulas to create numeric dates rather than depending on locale-specific parsing of text. Fractional serials represent times. Normalize imported timestamps to the intended business timezone before applying these calendar boundaries.
This example accepts integer years 1901 through 9998 and requires all six date cells to be numeric nonnegative serials. The sum also requires all six amounts to be numeric; refunds may be negative and zero amounts are valid. Counting transactions depends on dates, not amount signs.
| Payment Date (Col A) | Amount (Col B) | Year Bucket | Included for D2 = 2024? |
|---|---|---|---|
| 2023-12-31 | $1,200 | 2023 | Excluded (< 2024-01-01) |
| 2024-01-15 | $1,500 | 2024 | Included |
| 2024-02-29 (Leap Day) | $800 | 2024 | Included |
| 2024-11-20 | $2,200 | 2024 | Included (2024 Total = $4,500) |
| 2025-01-01 & 2025-06-10 | $950, $1,750 | 2025 | Excluded (> 2024-12-31; 2025 Total = $2,700) |
Sum Payments in Target Calendar Year D2
Include the start of the selected year and exclude the start of the next year. This includes December 31 afternoon and leap-day payments.
=IF(OR(NOT(ISNUMBER(D2)),COUNT(A2:A7)<>6,COUNT(B2:B7)<>6),"Invalid inputs",IF(OR(D2<>INT(D2),D2<1901,D2>9998,MIN(A2:A7)<0),"Invalid inputs",SUMIFS(B2:B7, A2:A7, ">=" & DATE(D2, 1, 1), A2:A7, "<" & DATE(D2+1, 1, 1))))Count Transactions in Target Calendar Year D2
Count rows using the same half-open interval. Amounts do not affect the date-based count.
=IF(OR(NOT(ISNUMBER(D2)),COUNT(A2:A7)<>6),"Invalid inputs",IF(OR(D2<>INT(D2),D2<1901,D2>9998,MIN(A2:A7)<0),"Invalid inputs",COUNTIFS(A2:A7, ">=" & DATE(D2, 1, 1), A2:A7, "<" & DATE(D2+1, 1, 1))))Try the example
Use the original values or change them to test your understanding.