Here I’m going to share with you a ‘magical’ SUMIF formula to sum by month in Google Sheets. I’m not exaggerating. See what I am going to do.
I am a huge fan of Google Sheets ArrayFormula. So I’m combining ArrayFormula with the SUMIF function to make it a single formula that can sum by month.
The benefit, you need not have to first convert the date to a month in a separate column to sum by month.
You May Like: Array Formula: How It Differs in Google Sheets and Excel.
Similarly, we can generate month criteria within the formula itself. In other words, you just need to key the formula in just one cell!
Sum by Month in Google Sheets
Before that, I am going to explain to you the simple or you can say the traditional method to sum by month in Google Sheets. Here is that.
Normal Way of Sum by Month in Google Sheets
Sample Data:
Steps:
1. First, we should extract the month from the date in a separate column, here column G. To do that use the following TEXT formula in cell G2.
=text(A2,"MMMM")
2. Now in order to expand the formula to below rows, instead of copy and paste, use ArrayFormula.
Just replace the above formula with the following array formula. Now the formula in Cell G2 must be as follows.
=ArrayFormula(text(A2:A7,"MMMM"))
In order to see the result, please refer to the screenshot below.
3. So we have converted the date to month. Now we can easily sum by month using SUMIF. But before that one more thing is required.
In Cell H2, use the above same formula but with UNIQUE. This is to generate the criteria for using in SUMIF.
=unique(ArrayFormula(text(A2:A7,"MMMM")))
4. Our criteria part is also ready! Now, as usual, we can use SUMIF to sum by month. To do that on Cell I2 apply the below formula.
=sumif($G$2:$G$7,H2,$F$2:$F$7)
5. You can either copy and paste the above SUMIF formula to the cells down (I3 and I4) or use ArrayFormula in I2 to automatically expand the result. Here is that SUMIF with ArrayFormula Combination.
=ArrayFormula(sumif($G$2:$G$7,$H$2:$H$4,$F$2:$F$7))
Now let us talk about the advanced part. I mean a single formula to do the above all jobs.
Advanced and Flexible Way of Sum by Month in Google Sheets
Here we can combine all the above formulas in one single formula. We are not combining the formulas as it is. There are minor changes like the use of Curly Braces. I will explain to you the difference. See the magical SUMIF formula below.
={{unique(ArrayFormula(text(A2:A7,"MMMM")))},
{ArrayFormula(sumif(ArrayFormula(text($A$2:$A$7,"MMMM")),
unique(ArrayFormula(text(A2:A7,"MMMM"))),$F$2:$F$7))}}
I will explain the above combination formula with the help of screenshots.
1. The first part of the formula has nothing to do with the Sum by Month. Refer below image. It’s there because we want to show the corresponding month against the summary value as the prefix.
That means the first part of the underlined formula just returns the unique month names in H1:H3. I’ve used Curly Brackets to separate this with the SUMIF part.
Now to the Second Part of the Formula.
You can see the SUMIF syntax in Cell C10. I’ve just put it there to explain to you the formula in detail. I’ve marked the SUMIF range, criterion, and the Sum range in the formula bar.
The SUMIF(range,
is the same formula under point # 2 under the title “Normal Way of Sum by Month in Google Sheets”. Also, Criterion is the same formula under point #3 there. The Sum range is the range from the original data range.
Finally, you can shorten the above formula. We only need one ArrayFormula at the beginning.
Shortened Sumif Array Formula to Sum by Month in Google Sheets:
=ArrayFormula({{unique(text(A2:A7,"MMMM"))},
{sumif(text(A2:A7,"MMMM"),
unique(text(A2:A7,"MMMM")),F2:F7)}})
Update Dated 5-Oct-2019 Based on User Feedbacks
How to Include Infinite/Open Ranges in Sumif
I have gone through user comments and could understand two issues they are facing.
- How to use an open range like A2:A instead of using a limited range like A2:A7 in the formula.
- A Sumif formula to sum by month and year.
I am going to address both the issues here.
Open Range in Sumif Sum by Month Formula
First of all, let’s see what happens when we use open ranges in the above Sumif shortened array formula.
Even though there are no dates in column A that falls in December, the Sumif sum by month formula returns a December month summary row in the output!
This is because of the use of the Text function. Did you try using a blank cell as the reference in Text formula to format a date to month text? For example, in our above Sumif example, the cell A8 is blank. Just try this formula.
=text(A8,"MMMM")
It would return “December”. We have this formula in the Sumif range and criterion. That is why the formula sums the December month and returns 0.
There are several methods to overcome this issue of summing the blank cells in Sumif sum by month. I am providing two options. Use the first one if you don’t have blank rows in your data range.
Option # 1 – If No Blank Rows
Replace A2:A wherever in the formula with the below Filter formula which filters out blank rows.
filter(A2:A,A2:A<>"")
Here is that awesome Sumif formula to sum by month in an open range in Google Sheets.
=ArrayFormula({{unique(text(filter(A2:A,A2:A<>""),"MMMM"))},
{sumif(text(filter(A2:A,A2:A<>""),"MMMM"),
unique(text(filter(A2:A,A2:A<>""),"MMMM")),F2:F)}})
The above formula won’t work correctly if you have blank rows (not at the end) in your data range. In that case, use this alternative one.
Option # 2 – Will Work Irrespective of Blank /Non-Blank Rows
=query(ArrayFormula({{unique(text(A2:A,"MMMM"))},
{sumif(text(A2:A,"MMMM"),
unique(text(A2:A,"MMMM")),F2:F)}}),"Select * where Col2>0")
Month and Year Summary Using Sumif from Date Column
Another requirement from users is how to include year also in the summary. So that if the date range spans across years, the month-wise total in each year will be separated. I mean, for example, ‘Amount’ in January 2019 and January 2020 will be summed separately.
It’s quite simple actually. Just change the Text formula formatting from “MMMM” to “MMMM-YY”. Didn’t get?
Here is the Sumif sum by month and year formula.
Option # 1 – If No Blanks
=ArrayFormula({{unique(text(filter(A2:A,A2:A<>""),"MMMM-YY"))},
{sumif(text(filter(A2:A,A2:A<>""),"MMMM-YY"),
unique(text(filter(A2:A,A2:A<>""),"MMMM-YY")),F2:F)}})
Option # 2 – Will Work Irrespective of Blank /Non-Blank Rows
=query(ArrayFormula({{unique(text(A2:A,"MMMM-YY"))},
{sumif(text(A2:A,"MMMM-YY"),
unique(text(A2:A,"MMMM-YY")),F2:F)}}),"Select * where Col2>0")
The above formula will work in the above said infinite range too. Here I have simply filtered out the
Actually using Google Sheets SQL similar Query, you can also get this month and year summary. Find it here – How to Group Data by Month and Year in Google Sheets.
You decide which one is better, I mean Sumif to sum by month and year or Query to sum by month and year.
Conclusion
If you want more control over the sum by month, I mean summary on a separate sheet and grouping follow this tutorial – Create Month Wise Summary Using Query Formula.
Any doubts please drop it in the comments. I’ll get back to you. Enjoy!
Similar: Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets.
The below are Columns A thru G.
03/01/21 | 5 x | x | x | x | #REF!
04/15/21 | 5 x | x | x | x | #REF!
04/17/21 | 5 x | x | x | x | #REF!
The following formula returns errors.
=ArrayFormula(text(A1:A3,"MMMM"))
This formula is documented as return the values: (March, April, April)
Hi, Bill Alexander,
The formula is correct as per my Sheet’s regional settings.
Check the error tooltip by hovering your mouse pointer over one of the REF errors.
If that doesn’t solve the problem, you can replicate the issue in a sample sheet and leave the URL below. I won’t publish the comment.
Hi Prashanth. Your formula worked like a magic, Thank you so much, Similarly how do I use the same formula to find the Average by month.
Hi, Javeed,
See if this tutorial helps?
Average by Month in Google Sheets (Formula Options)