where amount (C5:C16) and date (B5:B16) are named ranges. Note: as explained below, the values in E5:E10 are valid Excel dates, formatted to display the month name only. Both solutions use the named ranges amount (C5:C16) and date (B5:B16) for convenience only, you can also use absolute references instead, or an Excel Table with structured references. Also note that the values in E5:E10 are valid Excel dates, formatted to display the month name only. You can use a custom number format, to display these dates in any way you like.
SUMIFS solution
The SUMIFS function can sum values in ranges based on multiple criteria. The basic function signature for SUMIFS looks like this: In this case, we need to configure SUMIFS to sum values by month using two criteria: one to match dates greater than or equal to the first day of the month, one to match dates less than or equal to the last day of the month. To simplify the logic for the last day of month, we use the EDATE function to advance one full month to the first of the next month, then check for dates less than that result. Since we already have the first day of each month in column E, we can pick up that value directly from cell E5. To get the last day of the month, we use the EDATE function like this: The value of the second argument, months, is 1 because we want the first day of the next month. The final formula in F5, copied down, is: Notice we need to concatenate the dates to the logical operators. This is because the SUMIFS is in a group of functions that split criteria into two parts. The second approach below based on SUMPRODUCT doesn’t have this requirement. As the formula is copied down, we get a total for each month listed. The named ranges behave like absolute references and don’t change, and the reference to E5 is relative and changes at each new row. Note: we could use the EOMONTH function to get the last day of the current month, then use less than or equal to (<=) as the second logical operator. However, the time of the date returned by EOMONTH is technically midnight, so there is a danger of excluding dates with times that occur after 12:00 AM on the last of the month. Using EDATE ends is a simpler solution.
With hardcoded dates
If for some reason you need to use the SUMIFS function with hardcoded dates, the best approach is to use the DATE function like this: This formula uses the DATE function to create the first and last days of the month. This is a safer option than entering a date as text, because it guarantees that Excel will interpret the date correctly.
SUMPRODUCT option
Another nice way to sum by month is to use the SUMPRODUCT function together like this: In this version, we use the TEXT function to convert the dates to text strings in the format “mmyy”. Because there are 12 dates in the list, the result is an array with 12 values like this: Next, the TEXT function is used in the same way to extract the month and year from the date in E5: The two results above are then compared to each other. The result is an array of TRUE and FALSE values like this: This array indicates the dates in B5:B16 that are in the same month and year as the date in E5. The TRUE value in this array corresponds to the only date in January 2022. This array is then multiplied by the values in amount. The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so the final result inside SUMPRODUCT looks like this: With just one array to process, the SUMPRODUCT sums the array and returns 100 as the result in F5. As the formula is copied down, the relative reference E5 changes at each new row, and SUMPRODUCT generates a new result. One nice feature of this formula is that it automatically ignores time values that may be attached to dates, so there is no need to worry about the logic for dates+times that may occur on the last day of the month, as with SUMIFS above. Note: In the current version of Excel, which supports dynamic array formulas, you can substitute the SUM function for the SUMPRODUCT function. This article explains the details. Note: Why not use the TEXT function inside the SUMIFS formula above? The problem is that the SUMIFS function can’t handle an array operation in a range argument. You must provide an actual range.
Display dates as names
To display the dates in E5:E10 as names only, you can apply the custom number format “mmmm”. Select the dates, then use Control + 1 to bring up the Format Cells Dialog box and apply the date format as shown below:
This allows you to use valid Excel dates in column E (required for the formula) and display them how as you like.
Pivot Table solution
A pivot table is another excellent solution when you need to summarize data by year, month, quarter, and so on, because it can do this kind of grouping for you without any formulas at all. For a side-by-side comparison of formulas vs. pivot tables, see this video: Why pivot tables.
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.
