New Way to Sum Monthly Data into Quarters

How to sum monthly data into quarters using formulas. This video covers formulas suitable for all versions of
Excel, plus a dynamic array formula that spills the results for the entire table in one go.

🎓RELATED LESSONS:

LEARN MORE
===========

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

📢 Please leave me a COMMENT. I read them all!


🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

⏲ TIMESTAMPS
==============
0:00 The Problem
1:10 SUM fomrula - all versions
3:50 LET formula improvements
6:00 Dynamic Array Formula
11:05 Allowing for more rows & columns

[yvlove]

Thank you Mynda, Peter, and Sergei! I am obsessed with these new formulas and also spend hours trying to figure them out and make them work; it makes me question my knowledge and abilities too!! I can't wait to dig into the formulas provided to see what else I can learn and use! Thank you so much for sharing and inspiring! Mynda, my love for and knowledge of Excel grew into what it is today because I watched a video you made on dashboards about 8 years. My "excel" feet have never stopped running since! Thank you!!

[armstrongro]

Love your videos. Very easy to understand.
I have been doing Excel Development since 1993 and this is how I would have done it before the introduction of the new functions.
=SUM(OFFSET($C$3:$N$7, MATCH($B13, $B$3:$B$11, 0)-1, 0, 1, 12)*(ROUNDUP(MONTH($C$2:$N$2)/3, 0)=COLUMNS($C$12:C$12)))
by doing COLUMNS($C$12:C$12) you don't have to worry about the one person in a hundred (probably less) that likes to insert columns where ever they like.
with COLUMN(A:A) if someone inserts a column at column A everything gets wrecked. This way also gives the flexibility if the order of the items needs to be different in the quarterly summary.

[Michael_Alaska]

That was a lot to digest, but enjoyable to watch and follow along. For a moment I thought did I click on a Diarmuid Early video? Hats off to Peter, Sergei, and yourself. Thanks

[petercompton]

I haven't come across Sergei before but I just bow down in awe every time I see anything from Peter Bartholomew - really quite astonishing! And thank you Mynda for the very coherent explanation. I know that I will have to spend a bit of time getting my head round that one.

[AOC]

Mynda, once again you have demonstrated something which I can use in my real-world reporting scenarios. Thank you. Clear and concise as always.

[chiquita]

A ton of new formulas I didn't know about in this one. Kudos to all, very clear explanation!

[anv.]

Dear Mynda, so much information for seemingly quite easy calculation. amazing. clearly explained. Thank you.

[sankaliamayur]

It is like a magic. I need to work a lot to understand each function individually. But the explanation given at the bottom of video was very helpful to visualize how function works. Thanks a lot for such an informative tutorial.

[vijayarjunwadkar]

That's mind blowing, Mynda! Thanks to all of you, for sharing this rich knowledge with us. There are so many tricks in this one video, which will open up wide possibilities and applications, and that is really great! 🙂

[mohammedelsakally]

Thank you Mynda for your always valuable videos which are containing the new ideas to solve the problems we are facing all time when we are dealing with excel 🙏

[yourcfoguy]

Nicely done here Mynda, real easy to follow and informative!

[Quidisi]

HOLY COW!!! That was amazing!!!
Thank you so much for the time you, and the others spent figuring this out - and especially for explaining it so thoroughly in your video. Amazing stuff!!

[XLarium]

Peter is showing his amazing LAMBDA skills almost every day in the Techcommunity.

[ricos]

Really interesting Mynda, thanks.
When it gets to the single cell formula, I try to focus solely on reusability, and so I would have split this into two "reusable" lambdas. First would be a function called QUARTERS(dates, returnType), which would be a function that either returns quarter (QQ) or quarter and year (QQ-YY).
Second is a GROUPBY(array, columnToSum, includeTotals), which would take the array of quarter numbers from the QUARTERS() function and sum the values and include totals if requested.
Obviously this would have to be used in BYROW() to get the result for each item. Alternatively a better method might be to create an UNPIVOT() function to mirror PQ, which unravelled the table into Date, Item, Value before applying the above quarter and group functions.
This approach would leave you with three fairly reusable, and regularly so, functions that you could port across to other workbooks as required. You could obviously go further with group by, to include options for summing, average etc, and using hstack to group by multiple columns, perhaps with subtotals and such, but that'd keep you going for about ten videos!

[yokoyama]

Great content and great explanations! Thank you Mynda and the team!

[joewilson]

Nice video. Can I ask how you are able to "step into" the part written formula at 2:08 to show the 1, 1, 1, 2, 2, 2..etc

[ZAKISaidAliMaurice]

I found the demonstration very effective. Thank you very much for sharing your know-how.
i'm trying to adapt the same thing to calculate subtotals using BYCOL, but after 3 days i haven't managed to do it, so i'm writing this commentary in the hope that you'll be able to make a video along these lines.

[chrism]

Very clever and helpful, thanks Mynda! So awesome

[pc-doctor]

Makes my head spin. Amazing what can be done,

[ankursharma]

Loved It!
Loved It!!
Loved It!!!

THANK YOU!