Excel SUMIF or SUMIFS Formula to Sum Amounts Between Two Dates
Question details
Calculate the total of amounts whose corresponding dates fall between two specified dates.

- 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.
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.
Use SUMIFS to Total Amounts Between Two Dates
Use SUMIFS when you want a straightforward formula with inclusive start and end dates.
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.
Select the cell where you want the total and enter =SUMIFS($BH2:$BN2,$BG2:$BM2,">="&$BP$1,$BG2:$BM2,"<="&$BQ$1).
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 SUMPRODUCT to Test Dates and Add Matching Amounts
Use SUMPRODUCT to test both date conditions and multiply the results by the corresponding amounts.
Sum Amounts Between Dates in WPS Spreadsheet
Use SUMIFS in WPS Spreadsheet to total amounts paired with dates in an inclusive date range.
- 1. Open your workbook: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Identify your data: Find the date range, corresponding amount range, and cells containing the start and end dates.
- 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. Check the result: Press Enter and verify that the total includes the amounts paired with dates on or between the two boundaries.

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.




