Function Problems

Excel SUMIF or SUMIFS Formula to Sum Amounts Between Two Dates

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

Calculate the total of amounts whose corresponding dates fall between two specified dates.

Sum Amounts Between Two Dates in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains a range of dates, a corresponding range of amounts, and cells containing the start and end dates.
Observed behavior
The desired result is a total of the amounts paired with dates within the specified range, including both boundary dates.
Before you start

Make sure the start date, end date, and dates being tested are stored as actual dates. Check that the date and amount ranges have the same dimensions and that each date aligns with its corresponding amount.

Solution 1Recommended

Use SUMIFS to Total Amounts Between Two Dates

Use SUMIFS when you want a straightforward formula with inclusive start and end dates.

1
Check the ranges

In the worksheet, identify the date range, matching amount range, and cells containing the start and end dates. This example uses BG2:BM2 for dates, BH2:BN2 for amounts, BP1 for the start date, and BQ1 for the end date.

2
Enter the formula

Select the cell where you want the total and enter =SUMIFS($BH2:$BN2,$BG2:$BM2,">="&$BP$1,$BG2:$BM2,"<="&$BQ$1).

3
Adjust the references

Press Enter, then change the ranges or cell references in the formula bar to match your worksheet. The example ranges overlap, so confirm that each position represents the intended date-and-amount pair.

Use SUMIFS to Total Amounts Between Two Dates
Dates that include times: If dates include times and BQ1 contains a date without a time, replace the last condition with "<"&($BQ$1+1) to include all times on the end date.
WPS Spreadsheet

Sum Amounts Between Dates in WPS Spreadsheet

Use SUMIFS in WPS Spreadsheet to total amounts paired with dates in an inclusive date range.

  1. 1. Open your workbook: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Identify your data: Find the date range, corresponding amount range, and cells containing the start and end dates.
  3. 3. Enter SUMIFS: Select a result cell and enter =SUMIFS($BH2:$BN2,$BG2:$BM2,">="&$BP$1,$BG2:$BM2,"<="&$BQ$1), replacing the references with those in your sheet.
  4. 4. Check the result: Press Enter and verify that the total includes the amounts paired with dates on or between the two boundaries.
Calculate date-range totals with SUMIFS.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface to update formulas.
QA img-9

Frequently Asked Questions

Does the formula include the start and end dates?

Yes. The >= start-date and <= end-date conditions include both boundary dates when the tested values do not contain later times on the end date.

How do I include dates with times on the end date?

If the end-date cell contains a date without a time, use a condition earlier than the next day. In SUMIFS, replace the end condition with "<"&($BQ$1+1).

Why is my date-range total incorrect?

Check that the dates are stored as actual dates, the amounts are numeric, and the date and amount ranges have the same dimensions. In the formula bar, verify that each position in the date range corresponds to the intended amount.

Can I copy the formula to other rows?

Yes. In the example, $BP$1 and $BQ$1 keep the boundary dates fixed, while the row numbers in $BG2:$BM2 and $BH2:$BN2 can change when you copy the formula down.