HomeGoogle DocsSpreadsheetSum by Month in Google Sheets Using Combined SUMIF Formula

Sum by Month in Google Sheets Using Combined SUMIF Formula

Published on

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 to 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.

extract month from date in Google Sheets

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")))
create criteria from dates for summary

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)
sum by month using SUMIF basic formula

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.

curly brackets with sumif to get criteria on side

Now to the Second Part of the Formula.

all in one formula to sum by month

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.

  1. How to use an open range like A2:A instead of using a limited range like A2:A7 in the formula.
  2. 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.

Open Range in Sumif Sum by Month 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 # 2Will 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.

Demo Content and Formula

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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

47 COMMENTS

  1. 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.

  2. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here