Sum Data by Month (Not Year) in Google Sheets with SUMIF

This tutorial explains how to use SUMIF with other functions to sum data by month number or month name in Google Sheets.

In the formula, we will address a common issue with blank cells in your date range. Google Sheets functions can treat empty cells as 0, representing December 30, 1899.

This guide will walk you through the process step-by-step. If you’re short on time and prefer a ready-made solution, jump down to the final section titled “Step 4: SUMIF to Sum Data by Month and Filtering Out Blank Rows”

In that section, you’ll find a formula that just needs your date and amount range – it will handle the rest for you! However, you might miss some customization tips by skipping the earlier steps.

The sample data consists of dates in column A and amounts in column B. We will see how to use it in the steps below.

Step 1: SUMIF Criteria Part

Enter the following formula in cell D2 to return month names from January to December in D2:D13:

=ArrayFormula(TEXT(DATE(YEAR(TODAY()), SEQUENCE(12), 1), "MMMM"))

If you want to sum by month number, use the sequence part, which is =SEQUENCE(12), which will return the numbers 1 to 12 in D2:D13.

This formula works as follows:

The SEQUENCE function returns sequence numbers from 1 to 12. The DATE function uses it as the month component.

The syntax of the DATE function is DATE(year, month, day) where ‘month’ is SEQUENCE(12), ‘year’ is YEAR(TODAY()), and ‘day’ is 1.

The formula creates a list of beginning-of-month dates from January to December in the current year. The TEXT function formats it to month names from January to December. Both of these functions require the ARRAYFORMULA function to expand across the range.

The above formula returns the criteria for summing by month.

Step 2: Applying the SUMIF Formula to Sum by Month

If you have month names in D2:D13, enter the following SUMIF formula in cell E2 to sum data by month:

=ArrayFormula(SUMIF(TEXT(DATEVALUE(A2:A), "MMMM"), D2:D13, B2:B))

If you have the month numbers, then use the below formula:

=ArrayFormula(SUMIF(MONTH(DATEVALUE(A2:A)), D2:D13, B2:B))

Let me explain these two formulas. But first, let’s review the SUMIF syntax:

Syntax: SUMIF(range, criterion, [sum_range]).

In both formulas, the ‘criterion’ and ‘sum_range’ are the same. The only difference is in the ‘range’ part.

In the first formula, which sums the data by month name, the ‘range’ is TEXT(DATEVALUE(A2:A), "MMMM"). The DATEVALUE function returns date values in all rows containing dates and errors elsewhere. This addresses the blank cell issue mentioned at the beginning. The TEXT function converts those date values to month names.

In the second formula, which sums the data by month number, the range is MONTH(DATEVALUE(A2:A)). Here also, the role of the DATEVALUE is the same. Here the MONTH function returns the month number.

Please note that DATEVALUE, TEXT, and MONTH functions require ARRAYFORMULA support when used across a range.

Step 3: Combining the Criteria and SUMIF Results

The following two steps are optional. This step combines the above two formulas into one to facilitate filtering out blank rows in the next step.

Usually, you can combine two arrays horizontally using the HSTACK function as per the syntax: HSTACK(range1, [range2, …]).

Here, ‘range1’ will be the formula in cell D2, and ‘range2’ will be the formula in cell E2.

However, this won’t work as is because the E2 formula (SUMIF) is dependent on the D2 formula (criteria).

So, we will use the LET function to assign the name ‘months’ to the D2 formula and ‘total’ to the E2 formula. Within the E2 formula, we will replace D2:D13 (criteria reference) with the name ‘months’.

Delete the formulas in D2 and E2 and enter the following formula in D2:

=ArrayFormula(LET(
  months, TEXT(DATE(YEAR(TODAY()), SEQUENCE(12), 1), "MMMM"), 
  total, SUMIF(TEXT(DATEVALUE(A2:A), "MMMM"), months, B2:B), 
  HSTACK(months, total)
))

This is a single piece of code for summing by month in Google Sheets.

This follows the syntax: LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

Where:

name1: months
value_expression1: TEXT(DATE(YEAR(TODAY()), SEQUENCE(12), 1), "MMMM")
name2: total
value_expression2: SUMIF(TEXT(DATEVALUE(A2:A), "MMMM"), months, B2:B)
formula_expression: HSTACK(months, total)

Step 4: SUMIF to Sum Data by Month and Filtering Out Blank Rows

This formula returns the sum by month, excluding months where the total is 0.

When you use this formula, just replace A2:A and B2:B with the actual date and amount ranges, respectively. The formula will handle the rest.

=ArrayFormula(LET(
  months, TEXT(DATE(YEAR(TODAY()), SEQUENCE(12), 1), "MMMM"), 
  total, SUMIF(TEXT(DATEVALUE(A2:A), "MMMM"), months, B2:B), 
  combined, HSTACK(months, total),
  FILTER(combined, total<>0)
))

This is an improved version of the formula from Step 3.

Here, we use the FILTER function to filter the horizontally stacked arrays (named ‘combined’) for rows where the ‘total’ in the second column is not equal to 0.

Resources

47 COMMENTS

Bill Alexander September 7, 2021 At 8:29 am

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)

- Prashanth September 8, 2021 At 11:19 am
  
  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.
  
Javeed December 28, 2020 At 9:21 pm

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.

- Prashanth December 30, 2020 At 3:22 pm
  
  Hi, Javeed,
  
  See if this tutorial helps?
  
  Average by Month in Google Sheets (Formula Options)
  
Nil December 25, 2020 At 6:50 am

Hi Prashanth.

I have a problem with Sumifs. I’d like to have a sum by month and having two categories like expenses and income in months just like your but adding some category into it.

Btw your work is awesome I really like your idea about this.

Thanks for the reply.

- Prashanth December 25, 2020 At 10:22 am
  
  Hi, Nil,
  
  Thanks for sharing your sheet. I have inserted the necessary formulas in your sheet.
  
  Sample Data for other Readers.
  
  Date|Income_Expn|Amount
  1-Jan-2021 | Expenses | 1200
  2-Jan-2021 | Expenses | 2120
  3-Jan-2021 | Income | 5300
  1-Feb-2021 | Income | 1200
  2-Feb-2021 | Expenses | 1100
  3-Feb-2021 | Income | 1200
  
  The above data are in A1:C7.
  
  The first criteria, that is the month name “Jan” is in cell E2 and the second criteria “Expenses” is in cell F1.
  
  Sumifs month-wise summary formula in cell F2.
  
  =ArrayFormula(sumifs($C$2:$C,month($A$2:$A),month(E2&1),$B$2:$B,$F$1))
  
  Enter “Feb” in cell E3 and drag the above formula to F3.
  
Jivaka Jayasundera October 14, 2020 At 5:11 am

Hi, Prashanth,

This is magic! Thanks for the brilliant formula. Does exactly what I need.

Great work.

Jivaka

- Prashanth October 16, 2020 At 10:10 am
  
  Hi, Jivaka Jayasundera,
  
  Thanks for your feedback!
  
russ July 19, 2020 At 4:53 am

Any reason why a Countif would not work here instead of the Sumif? I am trying to summarize the month and then get an average. Thanks.

- Prashanth July 19, 2020 At 8:44 am
  
  Hi, Russ,
  
  It seems you should use the Query function. The Countif may also work.
  If you provide an example of your problem, I can possibly provide you the best solution.
  
Russ July 19, 2020 At 3:13 am

Helped a ton thanks!

Rik January 16, 2020 At 9:28 am

=query(ArrayFormula({{unique(text(K3:K,"MMMM"))}, {sumif(text(K3:K,"MMMM"), unique(text(K3:K,"MMMM")),L3:M1000)}}),"Select * where Col2>0")

I’m using this formula to read 2 columns to sum but it is reading only the first Column L and not the M column.

- Prashanth January 16, 2020 At 10:43 am
  
  Hi, Rik,
  
  The SUMIF function only supports one sum range, so use multiple SUMIF formulas combined as below.
  
  =query(ArrayFormula({{unique(text(K3:K,"MMMM"))}, {sumif(text(K3:K,"MMMM"), unique(text(K3:K,"MMMM")),L3:L1000)},{sumif(text(K3:K,"MMMM"), unique(text(K3:K,"MMMM")),M3:M1000)}}),"Select * where Col2>0")
  
  - Rik January 16, 2020 At 10:55 am
    
    That code also works when I add the values of the 2 columns! Thank you so much!
    
Kosta October 9, 2019 At 6:20 pm

Hi,

Thank you very much for the quick reply!

I tried sort columns but didn’t work very well (all it change the position and randomly lose the date. If formula can do that automatically for me, that would be perfect.

Thank you again!

- Prashanth October 10, 2019 At 11:23 am
  
  Hi, Kosta,
  
  It worked for me on your shared Sheet! Here is an alternative using Query. It sorts the month column in ascending order too.
  
  =ArrayFormula(query({if(len(A2:A),eomonth(A2:A,0),),B2:B},"Select Col1,sum(Col2) where Col1 is not null group by Col1 order by Col1 Asc label Sum(Col2)'Total', Col1 'Month & Year' format Col1'MMM-YY'"))
  
  Best.
  
  - Kosta October 10, 2019 At 12:45 pm
    
    WOW, thank you!
    
    The service quality rating for you from 1 to 10 is 11! 🙂
    
    Thank you very much!
    
  - Amy June 18, 2021 At 9:42 pm
    
    Hi Prashanth,
    
    Could you please use your example (not the formula Kosta is using) to show how the formula would look adding the Query to sort the month in ascending order? And how it is added into the original formula?
    
    Thanks,
    Amy
    
    - Prashanth June 20, 2021 At 10:01 am
      
      Hi, Amy,
      
      Feel free to share the URL of your sample sheet and your hand-entered expected result. That would be easy for me to offer a solution.
      
      - Amy June 26, 2021 At 12:54 am
        
        Thanks, Prashanth,
        
        I created a “Fake” document with the exact formulas I am using but only a fragment of the number of entries.
        
        You will notice that we want the project numbers to be in order. The date signed is not in order, and that is what I would like to have appeared in the total by month array on the second tab, “Totals.”
        
        Yellow is the results I am getting, and Blue is what I would like it to do.
        
        
        Prashanth June 27, 2021 At 12:15 pm
        
        Hi, Amy,
        
        You have not given edit access. But I could test my formula on a copy of your sheet.
        
        =ArrayFormula(query({'2021 SFM'!C:C,if(datevalue('2021 SFM'!F:F),eomonth('2021 SFM'!F:F,0),)},"Select Col2,sum(Col1) where Col2 is not null group by Col2 label sum(Col1)'Total Contracted Sales', Col2'By Month' format Col2'mmm-yy'",1))
        
        Please check the tutorial linked above the “Conclusion” part of the above tutorial for the formula explanation.
        
Kosta October 9, 2019 At 3:12 pm

My problem is that I have to have sometimes columns blank with the date and price, and then the formula doesn’t work precisely.

I also need to go down per year and months, for example; Jan 19, Feb 19, Mar 19, until the end of the year, then the new year begins; Jan 20, etc.

TEST LINK:

… removed …

How can I ignore empty columns?

Thanks

- Prashanth October 9, 2019 At 4:36 pm
  
  Hi, Kosta,
  
  This formula seems to work.
  
  =query(ArrayFormula({{unique(text(A2:A,"MMMM-YY"))}, {sumif(text(A2:A,"MMMM-YY"), unique(text(A2:A,"MMMM-YY")),B2:B)}}),"Select * where Col2>0")
  
  For order by month, you may sort your date column.
  
  Best,
  
Stek October 4, 2019 At 8:50 pm

Hi,

Thanks for your blog post.

The formula returns the results in a column. How can I split months and sum?

Thanks

- Prashanth October 4, 2019 At 9:42 pm
  
  Hi, Stek,
  
  That may be due to the locale setting in the menu File > Spreadsheet settings. Before using my formula, set the Sheet’s Locale to the UK. Once you have seen the expected result, you can set the Sheet’s locale back to your original locale.
  
  If this doesn’t help, Share a copy of your Sheet (if doesn’t contain any personal or confidential data) with me.
  
  Best,
  
- Prashanth October 5, 2019 At 12:31 pm
  
  Hi, Stek,
  
  I have modified the formula on your example Sheet. Just replaced the ; with a ,.
  
  Also, I have updated this post. Now included some additional info and also my experiment Sheet. Hope you will like that.
  
  Best,
  
Bek September 11, 2019 At 5:41 pm

I am going to sum data by month and year using Sumifs because there are many criteria. But I don’t know how to do that. could you please help me with the solution?

- Prashanth September 12, 2019 At 9:11 am
  
  Hi, Bek,
  
  Assume my dataset has the following columns.
  
  Date | Product | Area | Amount
  
  The criteria are as follows.
  
  Month = 10, Year = 2019 (both in column A)
  Product = “Orange” (column B)
  Area = “South” (column C)
  
  The formula to sum the amount in column D based on the above Sumifs criteria.
  
  =ArrayFormula(sumifs(D2:D,year(A2:A),2019,month(A2:A),10,B2:B,"Orange",C2:C,"South"))
  
  Best,
  
Ab July 29, 2019 At 9:39 am

Hi I’m very new to ‘google sheets’ , was getting great help from this site, but still cannot figure something, here’s what I need if someone can please reply.

Looking for a formula to sum the most recent month and the sum since the beginning of this year, and I want these 2 cells to start counting anew every month and year.

Thanks

- Prashanth July 29, 2019 At 11:01 am
  
  Hi, Ab,
  
  You can use Sumif or Query. Looking for a sample Sheet to proceed further.
  
Obie June 2, 2019 At 10:04 pm

Thanks for all this!

My date/time values (col D) are in this format: 2019-01-01 00:00:00

I am able to get the unique months to show in the MMM format, but nothing is getting totaled (I get all 0s).

={{unique(ArrayFormula(text(D2:D367,"MMM")))}, {ArrayFormula(sumif(ArrayFormula(text($D$2:$D$367,"MMM")), unique(ArrayFormula(text(D2:D367,"MMM"))),$E$2:$E$367))}}

- Prashanth June 3, 2019 At 9:02 am
  
  Hi, Obie,
  
  I don’t find any issue with your formula. Can you share a copy of the Sheet with me?
  
  If you share, I encourage you to replace the original data with demo data.
  
Raul Leiria March 11, 2019 At 2:55 am

=SUMIF(FILTER(E2:E500; ARRAYFORMULA(TEXT(D2:D500; "MMMM")) = "march"); "<0")

Shawn December 9, 2018 At 11:12 pm

Perfect Prashanth! Thank you so much…this is exactly what I was hoping for. I do have a similar question adding an Array Formula which would reference this formula above if you have the time…

Same page as above, column T.

— link remove by admin —

I would like to take this formula;

=if(R6"",COUNTIF(D9:D,">="&DATE(2018,4,1))-COUNTIF(D9:D,">"&DATE(2018,4,30)),"")

and make it less “hard-coded” with the months and also use ArrayFormula with it.

It works with that same formula above but will give me the number of times April. For example, showed up in the array.

I just wanted something I didn’t have to hardcode the date into and it could read that same array and extract the month and still count how many times that month showed up. Any ideas?

- Prashanth December 10, 2018 At 10:58 am
  
  Hi,
  
  Use this Query.
  
  =query({D9:E},"Select Count(Col2) where Col1 is not null group by month(Col1) label count(Col2)''",0)
  
  If your data contains multiple years, then it would return the wrong output.
  
  - Shawn December 11, 2018 At 6:46 am
    
    Oh thanks, this works great! Would there be a better version for multiple years or would you recommend just updating the query table for the new year?
    
    - Prashanth December 11, 2018 At 8:43 am
      
      Hi, Shawn,
      
      Welcome!
      
      Please wait for my upcoming post! I’ll address your question in that.
      
    - Prashanth December 11, 2018 At 9:16 am
      
      Hi, Shawn,
      
      I thought of writing a new tutorial on month and multiple years wise summaries. But the fact is that this site already hosts a few tutorials on the same. Just Google “month and year wise summary in google sheets” and you can hopefully see my tutorials.
      
      If I post again, my other readers won’t like it.
      
      Thanks.
      
    - Prashanth December 14, 2018 At 11:06 am
      
      https://infoinspired.com/google-docs/spreadsheet/group-data-by-month-and-year-in-google-sheets/
      
Shwn April 30, 2018 At 5:48 am

This gives a formula parse error

- Prashanth May 1, 2018 At 8:54 am
  
  Hi Shwn,
  
  It may be due to the double quotes (if you copied the formula from this page). So you may please remove and retype the double quotes.
  Still the problem persists? Then please share me your sheet (limited dummy data) with edit access.
  
  Thanks.
  
  - Shwn May 16, 2018 At 2:11 am
    
    Thanks, Prashanth. I retyped the double quotes already, here is the sample spreadsheet
    
    — URL removed by admin —
    
    Thanks!
    
    - Prashanth May 16, 2018 At 8:44 am
      
      Hi Shwn,
      
      I think the formula working on your sheet. I guess that you want the sum by month formula to work on infinitive rows.
      
      Here is that formula customized for your data range. I’ve added this to your sheet.
      
      =query({{ArrayFormula(UNIQUE(IF(ISBLANK(C2:C) ,, text(C2:C,"MMMM"))))}, {ArrayFormula(sumif(ArrayFormula(text(C2:C,"MMMM")), unique(ArrayFormula(text(C2:C,"MMMM"))),D2:D))}},"Select * where Col2>0")
      
      Anyone can use this formula. In this, the date column is C2:C. The column to sum is D2:D.
      
      Thanks.
      
      - Shawn December 9, 2018 At 12:20 am
        
        Hi Prashanth, hope all is well. interestingly enough this formula has been working perfectly since May, for some reason, any time I add December I get this ARRAY_ROW parameter error. Do you think you could look at it and tell me what’s going on?
        
        
        Prashanth December 9, 2018 At 12:15 pm
        
        Hi, Shawn,
        
        Welcome back!
        
        Regarding the error, the ISBLANK makes the issue. So you can remove that from the formula and then it would work.
        
        Also, there is no need to use multiple ArrayFormulas. You can wrap the entire formula with one ArrayFormula. I have left the ArrayFormula there to make you understand how I combined the formula.
        
        Try this. In this formula, I have removed the Isblank and all the extra ArrayFormulas.
        
        =ArrayFormula(query({{UNIQUE(text(D9:D,"MMMM"))}, {sumif(text(D9:D,"MMMM"), unique(text(D9:D,"MMMM")),E9:E)}},"Select * where Col2>0"))
        
        But I recommend you to use the Query formula of which the tutorial link provided within the above post. That formula would sum the values in January 2018 and January 2019 separately.
        
        Thanks.
        
Lauren March 29, 2018 At 9:18 am

I want to leave it open to an array of the whole column (so like, A2:A, if I don’t want to include the header). But then it automatically generates “December” in my sum area, even if there is no entry for December. It seems like it is assuming any blanks are that 12th month? Is there a way to fix/avoid that?

- Prashanth March 29, 2018 At 10:51 am
  
  Hi,
  When we group a column that containing the date, it’s common that the blank cells considered as a past date, i.e. December 30, 1899. This issue is common with infinitive ranges as mentioned by you. Sumif is no exception in this case. But there is a fix.
  
  =query({{ArrayFormula(UNIQUE(IF(ISBLANK(A2:A) ,, text(A2:A,"MMMM"))))}, {ArrayFormula(sumif(ArrayFormula(text(A2:A,"MMMM")), unique(ArrayFormula(text(A2:A,"MMMM"))),F2:F))}},"Select * where Col2>0")
  
  This formula would address your above-said issue.