Use Excel SUMIFS to Sum Values Between Two Selected Dates
Question details
Calculate a total for values in a selected column by choosing a start date and an end date from drop-down lists.
- Product
- Microsoft Excel spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to sum values in columns B, C, or D only when the corresponding date in column A falls within a selected date range.
- Observed behavior
- The required result is a dynamic total that updates when the start date or end date selection changes.
Make sure the dates in column A are real spreadsheet dates, not text strings. Place the selected start date and end date in dedicated cells, such as F1 and G1, before entering the formula.
Use SUMIFS with selected start and end dates
Use this method when column A contains dates and you need a total that updates automatically for any selected inclusive date range.
SUMIFS evaluates each date in column A against the start date and end date, then adds only the matching values. The greater-than-or-equal-to and less-than-or-equal-to criteria include both selected dates.
Enter or select the start date in cell F1 and the end date in cell G1. To create drop-down lists, select F1 or G1, then use Data > Data Validation and choose a date list or a range containing valid dates.
Select the cell where the total should appear and enter =SUMIFS(B:B,A:A,">="&F1,A:A,"<="&G1). Press Enter to calculate the total for column B.
To total column C, replace B:B with C:C. To total column D, replace B:B with D:D while keeping A:A as the date criteria range.
Select a date in column A and check that the cell is formatted as a date through Home > Number Format. If SUMIFS returns an unexpected result, convert text dates to real dates before recalculating.
Sum values between selected dates with SUMIFS in WPS Spreadsheets
WPS Spreadsheets supports the SUMIFS function for date-based totals. Create start and end date selectors, then use criteria linked to those cells to total the matching values in any amount column.
- 1. Open the worksheet in WPS Spreadsheets: Open the workbook in WPS Office and confirm that the date field is in column A and the values to total are in column B, C, or D.
- 2. Set up start and end date cells: Use cells such as F1 and G1 for the selected dates. Select each cell and choose Data > Data Validation to set up a valid date list or reference a list of available dates.
- 3. Add the SUMIFS formula: Select the output cell and enter =SUMIFS(B:B,A:A,">="&F1,A:A,"<="&G1), then press Enter.
- 4. Adapt the formula to another value column: Replace B:B with C:C or D:D to calculate totals for a different column while retaining column A as the date criteria range.
Frequently Asked Questions
How do I make SUMIFS include both the start date and end date?
Use >= for the start-date criterion and <= for the end-date criterion, such as =SUMIFS(B:B,A:A,">="&F1,A:A,"<="&G1). This includes values dated on both F1 and G1.
Why does my SUMIFS formula return 0 for a date range?
Check whether the dates in column A are stored as text instead of real dates. Select the cells and use Home > Number Format to inspect their format, then convert text dates if necessary.
Can I use drop-down lists for the start and end dates?
Yes. Put the selections in cells such as F1 and G1, select each cell, and use Data > Data Validation to create a list from a range containing valid dates.
How do I sum column C instead of column B for the same dates?
Change only the sum range in the formula. For example, use =SUMIFS(C:C,A:A,">="&F1,A:A,"<="&G1) to sum column C.
