To sum by month has nothing different than How to SUM values between two dates using SUMIFS formula. However, this article shows you a more dynamic and specialized approach combining EOMONTH and SUMIFS functions that you don't need to guess how many days in a month to sum month.
If you only want to calculate the number of days in a month, please refer to this article: How to find number of days in month
Syntax
=SUMIFS(
sum data range,
date range,
">=" & first day of month,
date range,
"<=" & EOMONTH(
first day of month,
0
)
)
Steps
- Start with =SUMIFS(
- Select or type the range reference that includes the values to sum $C$3:$C$12,
- Continue with first criteria range – criteria pair with date range and 1st day of month $B$3:$B$12,">="&$E3,
- Enter second criteria range – criteria pair with date range and EOMONTH function $B$3:$B$12,"<="&EOMONTH($E3,0)
- Type ) to close SUMIFS function and press Enter to complete the formula
How
First of all, SUMIFS function adds values that meet single or multiple criteria. Ability to use criteria with logical operators like greater than or equal (>=) and less than or equal (<=) provides the way of adding values between values like sum month.
To filter dates in a month, we need dates for the first and last days of that specific month. Although the first day of the month is easy to guess, displaying a month like 11/1/2018 is not ideal. Thanks to formatting options of Excel, we can display a date by its full month name. Adding custom format "mmmm" to a date displays it by its long month name without changing its original value. To apply a custom format:
- Select the cell to be formatted and press Ctrl+1 to open the Format Cells dialog. An alternative way to do is by right-clicking the cell and then going to Format Cells > Number Tab.
- Under Category, select Custom.
- Type in the format code into the Type
- Finally, click OK to save your changes.
For more information on Number Formatting please visit: Number Formatting in Excel – All You Need to Know.
After adding the first days of months, it is time to enter criteria range-criteria pairs next. The first pair is easy: date range and the date of first day of the month.
$B$3:$B$12,">="&$E3,
The next criteria range-criteria pair should address the last of the month. We use the EOMONTH function that returns the last day of a month. The EOMONTH gets date and month arguments. The date is the same date we use in the first criteria and month argument is set to 0 to point exact date in the first argument.
$B$3:$B$12,"<="&EOMONTH($E3,0)
Because both criteria range-criteria pairs are set, it is time to use them in SUMIFS with a range that includes values to be summed to sum month.
=SUMIFS($C$3:$C$12,$B$3:$B$12,">="&$E3,$B$3:$B$12,"<="&EOMONTH($E3,0))
