# How to Sum by Month in Excel

If sum data by month in a table, we need to use a formula based on SUMIFS function. We can provide a set of conditions or criteria in SUMIFS function to sum data. To sum data by month, we need to add date range as criteria.

### Example:

See below screenshot, if we want to sum total amount by month, how can we do? ### Solution:

In C2, enter the formula =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,”>=”&A2,\$A\$2:\$A\$10,”<=”&EOMONTH(A2,0)). Let’s see how this formula works:

For SUMIFS function, =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …), in this instance, \$B\$2:\$B\$10 is the sum range, \$A\$2:\$A\$10 is the criteria range, criteria 1 is >=A2 (10/1/2020), criteria 2 is <=EOMONTH(A2,0), it is the last date of October (the last date in month of A2), to get the last date in month of A2, we use EOMONTH function here.

For EOMONTH function, =EOMONTH(start_date, months), it returns the last date of a month for a specific date. In this instance, EOMONTH(A2,0), with zero as months value, it returns the last day of a month provided by date in A2. As A2 is 10/1/2020, so this function returns 10/31/2020 at last. In fact, it equals to =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,”>=10/1/2020″,\$A\$2:\$A\$10,”<=10/31/2020″). If you enter EOMONTH(A2,1), it will return the last date of next month based on date in A2, for example, A2 is 10/1/2020, then EOMONTH(A2,1) returns 11/30/2020. If you enter EOMONTH(A2,-2), it will return the last day of month, two months before the month in A2, in this case it is 8/31/2020.

### Result:

Click Enter to get return value. Notes:

1. The formula also works if date format is other than mm/dd/yyyy. For example, we change date in November to different date format in table. Then formula is updated to =SUMIFS(\$B\$2:\$B\$10,\$A\$2:\$A\$10,”>=”&A5,\$A\$2:\$A\$10,”<=”&EOMONTH(A5,0)). It still works. 2. If we just want to sum data between a period, we can update formula to =SUMIFS(sum range, criteria range1,”>=”&start date, criteria range2,”<=”&end date). For example, to get amount between date 12/10/2020 and 12/12/2020. Enter the formula =SUMIFS(\$B\$2:\$B\$14,\$A\$2:\$A\$14,”>=”&A11, \$A\$2:\$A\$14,”<=”&A13). ### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
Related Posts

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter ...

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Sum by Formula If Cells Are Not Blank in Criteria Range

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how ...

Sidebar