In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be part of the final result. A good way to solve this problem is with the SUMIFS function.
Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates. All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below.
The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria. The generic syntax for SUMIFS looks like this:
=SUMIFS(sum_range,range1,criteria1) // 1 condition =SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions
In this problem, we need two conditions: One to test for dates greater than or equal to the date in cell E5, and one to test for dates less than or equal to the date in cell F5. To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Each condition requires a separate range and criteria, and operators need to be enclosed in double quotes (""). We start off with the sum range, which contains the amounts in C5:C16:
Next, we need to add the first criteria, for dates greater than or equal to cell E5. We add the range (B5:B16), then add the criteria:
Notice we need to enclose the logical operator in double quotes (""), and join the text to the cell reference with concatenation. This is a quirk of the SUMIFS function. If we enter this formula with a single condition as-is, we will get a sum of all amounts in C5:C16 that are greater than or equal to 15-Sep-2022, which is $32,050.
Next, we need to add the second criteria, for dates less than or equal to cell F5:
Note we use the same range (B5:B16), followed by the criteria, which is again concatenated to the cell reference. When we enter the formula, it returns $13,500, the total of Amounts in column C that are between 15-Sep-2022 and 15-Oct-2022, inclusive. If the dates in cell E5 or cell F5 are changed, the formula immediately calculates a new result.
With hard-coded dates
The safest way to hard-code dates into the SUMIFS function is to use the DATE function. To sum Amounts in column C that are between 15-Sep-2022 and 15-Oct-2022 with fixed dates, you can use a formula like this:
Notice we still need to concatenate the logical operators to the DATE function.