Excel SUMIFS date range formula - sum if between two dates

This tutorial demonstrates how to efficiently sum numerical data within a specified date range using Excel's SUMIFS function. Many users struggle with applying SUMIF to date ranges due to its single-criteria limitation; however, SUMIFS readily handles multiple criteria.

Key Applications:

• Summing data between two dates.
• Summing based on a date range relative to today's date.
• Incorporating additional criteria beyond date ranges.
• Understanding date criteria syntax within SUMIFS.
• Troubleshooting common SUMIFS date range errors.

Summing Data Between Two Dates:

The core SUMIFS formula for summing values between two dates (inclusive) is:

SUMIFS(sum_range, dates, ">="&start_date, dates, "

To exclude the boundary dates, use ">" and "=" and "

Example: To sum project budgets (column B) due between September 10th, 2020 and October 1st, 2020 (column C):

=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "

Alternatively, input start and end dates in cells (e.g., F1 and G1) for greater flexibility:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "

For enhanced precision, utilize the DATE function:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "

Dynamic Date Ranges Based on Today's Date:

For sums within a dynamic range (e.g., last 7 days), employ the TODAY function:

To include today:

=SUMIFS(B2:B10, C2:C10, ">="&TODAY()-7, C2:C10, "

To exclude today:

=SUMIFS(B2:B10, C2:C10, ">="&TODAY()-7, C2:C10, "

Adding Additional Criteria:

To incorporate extra conditions (e.g., project names containing "tip"), extend the formula:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "

SUMIFS Date Criteria Syntax:

• Dates directly in criteria: Use logical operators (>, >=, =9/10/2020").
• Dates in cells: Concatenate operators and cell references using ampersands (e.g., ">="&F1).
• Dates from functions: Concatenate operators and functions (e.g., ">="&TODAY()).

Troubleshooting:

• Incorrect Results: Verify date formats (true dates, not text), number formats (true numbers, not text), and formula logic (correct operators and ranges).
• #VALUE! Error: Ensure all ranges are of equal size.

This comprehensive guide empowers you to effectively utilize SUMIFS for date-based calculations in Excel. A practice workbook is available for download to solidify your understanding.

The above is the detailed content of Excel SUMIFS date range formula - sum if between two dates. For more information, please follow other related articles on the PHP Chinese website!