A timestamp includes more information than the date shown by its number format. A record displayed as 28 September may contain a time later that day. Testing it for equality with midnight can therefore miss records that belong in the daily total.

Start with genuine numeric Excel dates and times, rather than date-looking text. Excel represents time as a fraction of a day. Decide which calendar day the source timestamps represent before calculating; a formula cannot resolve an undocumented time-zone convention.

Set a start boundary and an exclusive end boundary

Suppose timestamps occupy A2:A5 and quantities occupy B2:B5. Put the intended date, with no time component, in F1. In a separate output cell, use:

=SUMIFS(B2:B5,A2:A5,">="&F1,A2:A5,"<"&(F1+1))

The first condition includes the start of F1’s day. The second stops before the next day begins. SUMIFS adds quantities whose corresponding timestamp satisfies both conditions. Keep every range aligned to the same rows; Microsoft requires the criteria ranges and sum range to have matching dimensions.

The quoted comparison operator is joined to the cell value with &. It is not a comparison against the literal text F1. Formula examples here use commas as argument separators; use the separator required by your Excel locale.

Prove both midnight edges

In a disposable sheet, create four timestamp values using =F1, =F1+0.5, =F1+1-1/86400 and =F1+1 in A2:A5. These represent the start, midday, the final second, and the next midnight. Enter respective quantities 2, 3, 5 and 7 in B2:B5.

The expected total is 10: the first three records belong to the selected day, while the next midnight does not. Display enough date-and-time detail to inspect the test values rather than hiding every time component behind a date-only format.

Apply the rule to a limited real sample and compare its included records manually. Confirm that F1 has no accidental time and that the formula range reaches the newest records. Keep the reporting day and source time convention beside the result so a later reader knows exactly which interval the number describes.

Sources: Microsoft SUMIFS; Microsoft date and time values; Microsoft criteria references.