Exceljet

Quick, clean, and to the point

Sum by month

Excel formula: Sum by month
Generic formula 
=SUMIFS(range1,range2,">="&A1,range2,"<="&EOMONTH(A1))
Summary 

To sum a set of data by month, you can use a formula based on the SUMIFS function, with help from the EOMONTH function. In the example shown, the formula in F5 is:

=SUMIFS(amount,date,">="&E5,date,"<="&EOMONTH(E5,0))

where amount (C5:C16) and date (B5:B16) are named ranges.

Explanation 

In this example, the goal is to sum the amounts shown in column C by month using the dates in column B. In this case, we are taking into the account the year as well, so the subtotals will be for January 2021, February 2021, etc. To perform this task, we are using the SUMIFS function, which can sum numeric values based on multiple criteria.Below, we look at an alternative based on the SUMPRODUCT function, which is a bit more flexible.

Both solutions below use the the named ranges amount (C5:C16) and date (B5:B16) for convenience only, you can also use absolute references instead. Note that the dates in E5:E10 are first-of-month dates for each month we want to sum by. If you prefer to display month names only, you can do this with a custom number format, as explained below.

SUMIFS solution

The SUMIFS function can sum values in ranges based on multiple criteria.

In this case, we need configure SUMIFS to sum amounts 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. SUMIFS is in a group of functions that split criteria into two parts, so we also need to concatenate the dates to the logical operators. The general structure is below, where "first" represents the first day of a month, and "last" is the last day of a month:

=SUMIFS(amount,date,">="&first,date,"<="&last)

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 EOMONTH function. After making these substitutions, we have the final formula:

=SUMIFS(amount,date,">="&E5,date,"<="&EOMONTH(E5,0))

Notice EOMONTH is configured with zero for the months argument, since we want to stay in the same month.

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: if the dates you are working with are actually datetimes (dates with times), you will need to adjust the logic in the formula a bit to catch dates on the last day of the month that have time values:

=SUMIFS(amount,date,">="&E5,date,"<"&EOMONTH(E5,0)+1)

In this version, we add a day to the last day of the month to get the first day of the following month, and change the operator to greater than (>) instead of greater than or equal to (>=).

With hardcoded dates

To sum by month with a hardcoded month, you can use a formula like this:

=SUMIFS(amount,date,">="&DATE(2021,1,1),date,"<="&DATE(2021,1,31))

This formula uses the DATE function to create the first and last days of the month. Using DATE like this is safer than typing out a date as text, since it guarantees Excel will interpret the date correctly.

SUMPRODUCT option

Another nice way to sum by month is to use the SUMPRODUCT function together with the TEXT function like this:

=SUMPRODUCT((TEXT(date,"mmyy")=TEXT(E5,"mmyy"))*amount)

In this version, we use the TEXT function to convert the dates to "MMYY" text strings. Because there are 12 dates in the list, the result is an array with 12 values like this:

{"0121";"0221";"0221";"0321";"0321";"0321";"0421";"0421";"0421";"0521";"0521";"0521"}

which is then compared to the same kind of text string for the month in E5 ("0121"). The result is an array of TRUE and FALSE values. This array is then multiplied by the values in amount. The math operation coerces the TRUE and FALSE values to 1s and 0s, so the final result inside SUMPRODUCT looks like this:

=SUMPRODUCT({100;0;0;0;0;0;0;0;0;0;0;0}) // returns 100

The SUMIFS function can perform this kind of operation in a self-contained formula because SUMIFS requires a range for the dates; you can't substitute an array. One nice feature of this formula is that it automatically ignores time values that may be attached to dates, so there is no need worry about the logic for dates+times that may occur on the last day of the month, as with SUMIFS above.

Display dates as names

To display the dates in E5:E10 as names only, you can apply a 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:

Custom number format to display month names

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.