Sum by Month in Google Sheets Using Combined SUMIF Formula

0
130
Sum by Month in Google Sheets

Here I’m going to share 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 SUMIF to make it a single formula that can sum by month. The benefit, you need not have to first convert date to month in a separate column to sum by month. Similarly we can generate month criteria within formula itself. In other words, you just need to key in the formula in just one cell!

Sum by Month in Google Sheets

Before that I am going to explain you the simple or you can say traditional method to sum by month in Google Sheets. Here is that.

Normal Way of Sum by Month in Google Sheets

Sample Data:

Sample Data to Sum by Month in Google Sheets

Steps:

1. First we should extract the month from 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. Now the formula in Cell G2 is as follows. Refer the screenshot that follows the formula.

ArrayFormula(text(A2:A7,”MMMM”))

extract month from date in Google Sheets

3. So we have converted 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.

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 formula to cells down or use ArrayFormula to automatically expand the result. Here is that SUMIF with ArrayFormula Combination.

ArrayFormula(sumif($G$2:$G$7,H2:H7,$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. I will explain you the difference. See that 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 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 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 you the formula. I’ve marked the SUMIF range, criterion, and 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” above. Also Criterion is the same formula under point #3. Sum range is the range from the original data range.

If you want more control over the sum by month, I mean summary on a separate sheets and grouping follow our below tutorial.

Create Month Wise Summary Using Query Formula

Any doubts please drop it in the comments. I’ll get back to you. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here