The result is $13,500, the sum of Amounts in the range C5:C16 when the date in B5:B16 is between 15-Sep-22 and 15-Oct-22, inclusive. 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.
SUMIFS function
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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
