Excel Sum by Date: Formulas for Daily and Monthly Totals

Formulas & functions · Updated November 18, 2026

To sum values for a date in Excel, use SUMIF with the date column as the criteria range and the amounts column as the sum range. For dates that may include times, use SUMIFS with a start-of-day condition and an exclusive next-day condition; that reliably includes every time on the chosen date.

Set up the data and report area

Assume transaction dates are in column A and amounts are in column B, with headers in row 1. Put a date to summarize in D2 and the result formula in E2. For example, if A2:A6 contains June 1, June 1, June 2, June 30, and July 1, and B2:B6 contains 120, 80, 50, 200, and 40, a total for June 1 should be 200. A June total should be 450.

Use actual Excel dates, not text that merely looks like a date. Excel stores a date as a serial number, and a cell’s number format controls how that value appears. If dates are imported as text, they may look correct while failing to match a date criterion. Format the date column and report dates as dates, and format the amount column as Currency, Accounting, or Number as appropriate. Formatting affects display, not the underlying values used in the calculation.

Keep the date and amount rows aligned: each amount must be on the same row as its transaction date. You can convert the source range into an Excel Table using Insert > Table. Tables expand when new rows are added, which can make formulas easier to maintain than fixed ranges; regular cell ranges work just as well for a small, stable list.

Sum values for an exact date with SUMIF

If the source date cells contain dates without time-of-day values, SUMIF is the simplest option. With transaction dates in A2:A100, amounts in B2:B100, and the date to summarize in D2, enter this in E2:

=SUMIF($A$2:$A$100,D2,$B$2:$B$100)

The function checks each cell in A2:A100 against D2 and adds the amount from the corresponding row in B2:B100 when the dates match. With the example transactions above and June 1 in D2, it returns 200. The dollar signs keep the source ranges fixed if you copy the formula down to summarize other dates. D2 remains relative, so it changes to D3, D4, and so on.

To build a daily summary, list each date you want to report in D2, D3, and subsequent rows, then fill the formula down in column E. You can enter dates manually, copy them from the source, or use Excel’s Remove Duplicates command on a copy of the date column. Microsoft 365 also supports dynamic-array functions such as UNIQUE, but a manually maintained date list can be easier to review and control.

SUMIF does not require the date criteria to be typed directly into the formula. Referencing a cell such as D2 is generally more convenient: changing the report date updates the result without editing the formula. It also avoids ambiguity between regional date-entry formats such as month/day/year and day/month/year.

Include timestamps with a date range

A cell that displays as June 1 may also contain a time, such as June 1 at 3:45 p.m. In that case, an exact match against midnight on June 1 will not match the timestamp. The cell display can hide this distinction. Use a lower boundary at the selected date and an upper boundary just before the next date instead.

With transaction dates and times in A2:A100, amounts in B2:B100, and the day to summarize in D2, put this formula in E2:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&D2,$A$2:$A$100,"<"&D2+1)

This includes values at or after midnight on D2 and excludes values at midnight on the following day. Thus it captures all times during the selected calendar day without needing to specify the final second of the day. In the example, if the two June 1 entries occur at different times, the result is still 200. The same formula also works when the source cells contain dates without times.

SUMIFS takes the sum range first, followed by pairs of criteria ranges and criteria. The ampersand joins a comparison operator such as ">=" to the date in D2. The next criterion uses D2+1, which represents the following date. The beginning is inclusive and the ending is exclusive, an approach that avoids missing transactions with fractional seconds or other stored time precision.

Sum by month or another date period

For a monthly report, put any date in the desired month in D2—for example, June 1 or June 15—and use the first and last boundaries of that month. With date-times in A2:A100 and amounts in B2:B100, enter this formula in E2:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&EOMONTH(D2,-1)+1,$A$2:$A$100,"<"&EOMONTH(D2,0)+1)

EOMONTH(D2,-1)+1 calculates the first day of D2’s month, while EOMONTH(D2,0)+1 is the first day of the next month. The exclusive upper limit includes every timestamp in the month. For the sample transactions, a date in June in D2 produces 450: 120 + 80 + 50 + 200. July’s 40 is outside the range.

This approach works for months of different lengths and across year boundaries, so you do not need to hard-code the number of days. For a quarter or a custom period, use a start date and the first date after the period as the two boundaries. The same principle applies: include dates greater than or equal to the start, and exclude dates greater than or equal to the next period’s start.

Choose between SUMIF, SUMIFS, and a PivotTable

Use SUMIF for one condition, such as one exact date in clean date-only data. Use SUMIFS when you need a date range, timestamps, or additional conditions. For example, a report might total one date only for a particular department or category. Add another criteria-range and criteria pair to SUMIFS for each additional condition, keeping the sum range and all criteria ranges the same size.

A PivotTable is useful when you need to explore many dates or summarize a large dataset by day, month, category, or other fields without writing a separate formula for every result. Select a cell in the source data and choose Insert > PivotTable. Put the date field in Rows and the amount field in Values, then check that the Values field is summarized by Sum rather than Count. If Excel treats the date as text or groups unexpectedly, correct the source dates and refresh the PivotTable. PivotTables are interactive summaries; SUMIF and SUMIFS are often preferable when the result must appear in a particular report cell or update directly from criteria cells.

For a formula-based report, use bounded ranges sized to cover the data or use an Excel Table so that added records are included as the table grows. Whole-column references can be convenient, but many formulas over entire columns may increase calculation work in a large workbook. Whatever method you choose, verify that new source rows are inside the ranges being summarized.

Enter, copy, and check the formula

On Windows, select E2, type the formula, and press Enter. On Mac, the same formula entry process applies; the main formula syntax is the same. Excel’s keyboard shortcuts can differ for other tasks, but no special shortcut is required to calculate a date total. If your Excel regional settings use semicolons as function argument separators, Excel may display or require semicolons instead of commas; the formulas here use the standard comma-separated syntax requested for Excel.

  • Confirm that D2 is a real date and not a text string.
  • Check that A and B cover matching rows and that the amount cells contain numbers.
  • Copy the formula down beside each report date, keeping the source ranges absolute.
  • Compare one result with a small manual addition or filter the source to the same date range.

For the sample data, a daily formula for June 1 should return 200, and the monthly formula for June should return 450. If the result is zero when values clearly appear on that date, inspect the stored date values and times before changing the formula. If it is too large, check for duplicate source rows, an incorrect report boundary, or a range that includes unrelated records.

Common date-summing problems

Dates stored as text: imported data may contain text rather than Excel serial dates. Changing the number format alone does not convert text into dates. Depending on the source and regional format, use Data > Text to Columns to convert the column, or convert the values with an appropriate date-parsing method. Verify a few converted cells before applying the summary formula to the entire dataset.

Hidden time values: formatting a timestamp to show only a date does not remove its time. If exact-date SUMIF misses those rows, use the SUMIFS start-and-next-day formula. You can reveal the time by temporarily applying a date-and-time number format to the source cells.

Amounts stored as text: numbers imported with currency symbols, spaces, or inconsistent separators may not be included as numeric values. Convert the source amounts to numbers and check a few cells. Also check for errors in the source ranges, because errors can prevent a result from calculating normally.

Unexpected totals: confirm that the criteria range and sum range begin and end on the same rows. Look for blank dates, duplicate transactions, and report dates that are accidentally text. A cell formatted as a date can still contain a different underlying date than it appears to, so compare the source and criteria values rather than relying only on their display.

Frequently Asked Questions

How do I sum a column by date in Excel? Put the date to summarize in a cell such as D2, then use SUMIF with the date range, D2, and the amount range. If source dates may contain times, use SUMIFS with criteria from the selected date through, but not including, the next date.

Can Excel sum dates that include times? Yes. Use a lower criterion of greater than or equal to the date and an upper criterion of less than the following date. This includes all timestamps on the selected day, even if the time is hidden by cell formatting.

How do I total sales by month? Use SUMIFS with a start boundary at the first day of the month and an exclusive boundary at the first day of the next month. EOMONTH can calculate those boundaries from any date in the month, including for months with different lengths.

Why does SUMIF return zero for a date? Common causes are dates stored as text, a mismatch between a date-only criterion and timestamps in the source, or amounts stored as text. Check the underlying values and use a date-range SUMIFS formula when the source includes times.

Can I sum by date and category at the same time? Yes. Use SUMIFS and add a criteria range for the category column and a criteria cell for the category to include. Keep the date boundaries as criteria too, and ensure the sum range and each criteria range cover corresponding rows.

Should I use a formula or a PivotTable? Use a formula when you want a fixed report layout or a total that responds directly to selected criteria. Choose a PivotTable when you want to rearrange, group, and explore summaries across multiple fields. Both methods depend on valid date values and numeric amounts in the source data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.