About this article
This article was created using an automated generation workflow leveraging generative AI. It is based on the SUMIFS syntax and multi-condition summation from Microsoft Support. Because daily aggregation examples using date-time boundary conditions are not documented in the provided reference materials, the following formulas and expected results have not been verified on a physical device.
Verification Status: spec_checked— The SUMIFS syntax has been confirmed via official information. Aggregation examples using date-time ranges have not been executed.
When date-time column values include timestamps, strict matching based on the date alone may fail to aggregate line items for the same day. For example, if the target cell to aggregate is 2026/10/20 00:00 and the line item is 2026/10/20 09:15, they do not match because their time components differ.
For daily aggregation, specify the range as greater than or equal to 00:00 AM on the target date and less than 00:00 AM on the following day. This includes the lower bound while excluding the upper bound. This approach ensures all times up to the very end of the target day are included in the range while excluding line items recorded at exactly 00:00 on the following day.
First, Try with Four Line Items
In a new worksheet, enter the following dummy line items. Place the date and time in column A and the sales in column B. Enter the date you wish to aggregate, 2026/10/20, in cell D2.
| Column A: Date and Time | Column B: Sales |
|---|---|
| 2026/10/20 00:00 | 100 |
| 2026/10/20 09:15 | 250 |
| 2026/10/20 23:59 | 300 |
| 2026/10/21 00:00 | 900 |
Enter the following formula into E2.
=SUMIFS($B$2:$B$5,$A$2:$A$5,">="&D2,$A$2:$A$5,"<"&D2+1)
Expected output (unexecuted): E2 is expected to be 650. It sums the three rows for October 20 and excludes 900 at 00:00 on October 21.
SUMIFS is a function that sums the values in the corresponding sum range for rows that meet the criteria within the specified criteria range. In this formula, the first argument $B$2:$B$5 is the sum range, and the second argument $A$2:$A$5 is the criteria range. The following ">="&D2 and "<"&D2+1 are the two criteria for that criteria range.
">="&D2targets values at or after the same time as the date in D2."<"&D2+1targets values before the date in D2 plus one day.
Excel treats dates and times as numbers, where time is represented as a decimal fraction of a day. Therefore, if D2 contains only a date, D2+1 is 00:00 AM of the next day. By setting the upper limit to the day after the date and comparing with <, values in the 23:00 hour are included, but exactly 00:00 on the following day is excluded.
In the formula, & concatenates the comparison operator and the cell value to create the criteria string. For example, if D2 is October 20, the lower limit criterion is "greater than or equal to October 20" and the upper limit criterion is "less than October 21." The fixed range $ ensures that the reference does not shift when the formula is copied to another cell. If you are creating a table that copies the summary date to cells other than D2, adjust the date cell referencing method to suit your purpose.
Reading boundaries on a timeline
The following figure shows the target range and upper limit for October 20. You can verify how boundaries are handled, specifically that 00:00 on the following day is not included in the previous day.
flowchart LR
A["10/20 00:00<br/>下限：含む"] --> B["10/20の日中<br/>対象範囲"] --> C["10/21 00:00<br/>上限：含まない"]
The time on the left edge is included in the target, and the time on the right edge is not included. The figure represents the boundary of the aggregation range. Whether a detail record falls within that range assumes that the value in column A is recognized as a date and time by Excel.
Comparing the exact match formula and the result
Let us compare this with a case where the condition is changed to an exact date match using the same detail records.
=SUMIFS($B$2:$B$5,$A$2:$A$5,D2)
This formula searches for rows where the value in column A equals D2. If D2 is October 20 at 00:00, records with times such as 09:15 or 23:59 will not match. In the dummy table above,Expected example (not executed)is100, which totals only the row for 00:00. On the other hand, the range condition formula is expected to total the three rows of the same day as650.
When verifying the difference, prepare rows for 00:00 of the target day, daytime, and the 23:00 hour, as well as a row for 00:00 of the following day. Verify whether the result of the range condition satisfies both "includes the three rows of the target day" and "does not include 00:00 of the following day." Even when increasing the number of rows, adding one detail row outside the target aggregation day to check the boundary evaluation makes it easier to notice errors in the upper limit condition.
Check again by changing the date
Next, change D2 to2026/10/21. In the four-row example,Expected example (not executed)results in a total for the next day of900. This is because 00:00 on October 21 is included in the lower limit of the new aggregation day. You can confirm that the same row excluded as the upper limit of the previous day becomes the target for the following day's aggregation.
This comparison shows that the formula does not simply "ignore the time," but rather extracts the interval from the start of the specified day to the start of the following day. By replacing the date cell and checking the results, you can verify whether the daily boundaries are contiguous. Note that if you enter a specific time in D2, the start position will no longer be 00:00. Enter only the date in cells used for daily aggregation.
When totals do not match, check the value type and range
Even if the cell format displays a date and time, if the value is actually text, it may not be possible to compare it with numerical date ranges as expected. Since this cannot be determined by the display format alone, use another cell to=ISNUMBER(A2)Use this to verify whether A2 is recognized as a numerical value. If it returns FALSE, check the conversion settings during CSV import. If conversion is required, it is easier to revert changes by performing conversions and verifying aggregation results on a copied table rather than modifying the source data directly.
Additionally, ensure that the sum range and each criteria range have the same size in SUMIFS. For example, if the sum range is B2:B5, the date/time criteria range should also be set to A2:A5. Mismatched start or end rows for ranges can lead to mismatched value alignments or unintended ranges being aggregated.
In SUMIFS, the sum range is the first argument. When changing formulas from SUMIF, verify that the argument order has not been mixed up. When adding criteria, continue with pairs of "criteria range, criteria". Official documentation states that up to 127 pairs of ranges and criteria can be specified, but when adding criteria other than dates, the respective range sizes must still match.
Date input may be affected by regional settings and input methods.10/11/2026Avoid ambiguous inputs such as this, and verify that D2 is recognized as the intended date. If an error occurs when pasting formulas, also check the argument separator characters used in existing formulas within the workbook.
Before using for operational aggregation
If daily sales reports contain timestamps in the line items, a range condition of greater than or equal to the target day and less than the following day is more suitable for your purpose than an exact match. By referencing a cell for the aggregation date, you can change that cell to review daily results. When adding conditions such as sales representatives or products, also append those criteria range and criteria pairs to SUMIFS, ensuring that each range corresponds to the same rows.
During closing processes, do not rely solely on aggregated values; filter the line items by the target date and cross-check the rows and amounts. In particular, verify that dates are not stored as text, that rows for midnight (0:00) of the following day are not incorrectly included in the previous day, and that range boundaries extend to the correct rows. If there are many line items or the source data format is unstable, establish the date/time conversion methods after import and the cross-checking procedures before incorporating aggregation formulas into your workflow.
Summary
To aggregate sales details with timestamps on a daily basis, use SUMIFS to specify greater than or equal to 0:00 on the target date and less than 0:00 on the following day. First, verify using dummy details that times on the same day are summed correctly and that the midnight (0:00) entry of the following day is excluded. When applying to actual data, verify that dates/times are recognized as numbers and that target rows match aggregated values before using them for closing operations.
