This is an advanced SUMIF tutorial. Let’s see how to use SUMIF to sum by month and year.

In Google Sheets, the grouping of data is something exciting as there are different formula options. I’ve shared many of them already. Please refer the links shared at the last part of this tutorial.

In this Spreadsheet tutorial, I’m going to explain to you how to use Google Sheets SUMIF to sum by month and year.

The formula for Sum by month and Sum by month and year are different. I’m going to share with you that wonderful tips here. Once learned, this formula can be a time saver for many of you.

Before going to explain to you how to use Google Sheets SUMIF to sum by month and year, I’ve detailed my above concept with the help of screenshots below.

Sample Data:

In this sample data (Sheet1), in Column A, you can see different dates that spread across the year 2016 to 2018.

Here a month wise summary will return wrong output as the years are different. Here what we may want to use is a month and year wise summary.

Screenshot 1:

Result: 

In this result (Sheet2), the SUMIF formula to sum by month and year is in cell B2. I’ve manually filled column A with months in this sheet.

Based on these months (criteria) and the year input in cell D1, the Sumif formula in B2 will sum the Sheet1 Column B (sum_range).

Screenshot 2:

How to Use SUMIF to Sum by Month and Year in Google Sheets

So here is that SUMIF formula in cell B2.

=ArrayFormula(sumif(text(Sheet1!$A$2:$A,"MMMM-YYYY"),A2:A13&"-"&D1,Sheet1!$B$2:$B))

Formula Explanation

The Syntax of the Google Sheets SUMIF formula.

SUMIF(range, criterion, [sum_range])

Now take a look at my above formula. In the formula, as per the syntax, the arguments are as below.

Range:

text(Sheet1!$A$2:$A,"MMMM-YYYY")

This formula converts the Year in Sheet1 Column A to the Month-Year format. Please refer to the following screenshot to see how it works.

I have a little bit modified the formula as I am applying it independently in a new sheet. The changes are in the use of ArrayFormula and the ranges.

Screenshot 3:

Criterion:

A2:A13&"-"&D1

In Sheet2 Column A (please refer above Screenshot 2), I have entered the month in text string as Criteria. It’s like “January”, “February” etc.

This criterion formula changes the existing criterion in the below format.

Screenshot 4:

Sum_Range:

Sheet1!$B$2:$B

It’s the range to sum based on the criteria (Screenshot 4) and range (screenshot 3). That’s all. Hope you could learn how to use Google Sheets SUMIF to Sum by Month and Year.

Conclusion

The above SUMIF formula is for month and year wise summary. You can shorten it if you want to use it in only for Month wise summary.

If your date range falls within a single year you can use the formula as below. If I consider the above same sample data range in Sheet1, the formula would be as follows.

Remember! This formula doesn’t consider the year in date column in Sheet1.

=ArrayFormula(sumif(text(Sheet1!$A$2:$A,"MMMM"),A2:A13,Sheet1!$B$2:$B))

Here is my Sample Google Sheet that contains the formula to sum by month and year- Sample Sheet.

More Topics Related to Month Wise Grouping

1. How to Group Data by Month and Year in Google Sheets.

2. Month Wise Formula Based Pivot Table Report in Google Sheets.

3. How to Create A Weekly Summary Report.

4. Sum by Month in Google Sheets Using SUMIF.