New Way to Sum Monthly Data into Quarters

[@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.

[@MyOnlineTrainingHub]

Thanks so much for sharing 🙏😊

[@petercompton538]

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.

[@MyOnlineTrainingHub]

Glad you enjoyed the video, Peter 🙏😊 have fun deciphering the files.

[@ricos1497]

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!

[@MyOnlineTrainingHub]

Great ideas and in fact if you were to look at Peter's actual file you'll see he stores the lambdas in the name manager which vastly simplifies the formulas, but for the purpose of teaching, I moved it all into one formula so I could step through it together. I like the idea of an UNPIVOT lambda that could be reused 👍

[@ricos1497]

@@MyOnlineTrainingHub I think the following should work as an UNPIVOT function: =LAMBDA(data,includesRowTotals,includesColTotals, LET( d,data, r,ROWS(data)-includesRowTotals, c,COLUMNS(data)-includesColTotals, seqD,SEQUENCE((r-1)*(c-1)), indC,MOD(seqD-1,c-1)+2, indR,ROUNDUP(seqD/(c-1),0)+1, colVector,INDEX(data,1,indC), rowVector,INDEX(data,indR,1), valuesVector,INDEX(data,indR,indC), return,HSTACK(colVector,rowVector,valuesVector), return) )(B4:N9,,) where data is your table, and "includesRow/ColumnTotals" will just ignore the respective column or row if there is one (0 for none, 1 for total col/row). I'm sure there is a better way. Getting into it just now, I should have created a "REPEATVECTOR" function to create the respective vectors! That's the wonder of LAMBDA I guess.

[@ricos1497]

Yes, there was a lot more simple function! UNPIVOT: =LAMBDA(data,includesRowTotals,includesColTotals, LET( d,data, r,ROWS(data)-1-includesRowTotals, c,COLUMNS(data)-1-includesColTotals, return,MAKEARRAY(r*c,3, LAMBDA(i,j,INDEX(d,IF(j=1,1,ROUNDUP(i/c,0)+1),IF(j=2,1,MOD(i-1,c)+2)))), return) )(B4:N9,,) I forgot about MAKEARRAY, which saves on a sequence, and I also forgot that INDEX is very clever, and can take IF statements within its parameters and output an array of vectors.

[@anv.4614]

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

[@MyOnlineTrainingHub]

Thanks so much!

[@yvlove77]

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!!

[@MyOnlineTrainingHub]

Aw, your kind words are what keeps me going. Thank you so much. I'm grateful that I can share these videos with 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.

[@MyOnlineTrainingHub]

So pleased you enjoyed it 🙏😊

[@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

[@MyOnlineTrainingHub]

😁 thanks for watching, Michael! Glad you enjoyed it.

[@A8OC]

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

[@MyOnlineTrainingHub]

Awesome to hear 🙏

[@chiquita683]

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

[@MyOnlineTrainingHub]

Great to hear! 🙏😊

[@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! 🙂

[@MyOnlineTrainingHub]

Thanks for watching, Vijay! Glad you enjoyed it. 🤯😁

[@mohammedelsakally540]

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 🙏

[@MyOnlineTrainingHub]

Thanks for watching!

[@XLarium]

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

[@MyOnlineTrainingHub]

He's super helpful!

[@Friday_456]

​@@MyOnlineTrainingHub😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊😊

[@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.

[@MyOnlineTrainingHub]

Glad it was helpful! Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@juanbon5996]

Hi Mynda, greetings from Miami, Florida. My hat off to Peter and Sergei. I am, however, inclined to use Power Query and Pivot Tables, as I am a lot more confident in using thanks, to a great extent, to your videos.

[@MyOnlineTrainingHub]

Thanks so much for watching! I too prefer PQ and PivotTables 😉

[@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!!

[@MyOnlineTrainingHub]

Thanks for watching! So glad you liked it 😊

[@yourcfoguy]

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

[@MyOnlineTrainingHub]

Thanks so much, Josh!

[@RafaelSilva-pj6og]

Those guys are amazing. I've personally received help from Peter Bartholomew in the recent past and I can attest that his knowledge of excel formulas exceeds mine in orders of magnitude!

[@MyOnlineTrainingHub]

Mine too, Rafael! 😊 Thanks for watching.

[@adamgranato6541]

Thankyou so much. This really simply explained what i had been struggling with for a couple of hours. I have one question though, how do you have this work over a data set of multiple years. I am finding it summing all Q1, Q2, Q3, Q4 for all years. Is there a way to specify discreet years?

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@yokoyama7590]

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

[@MyOnlineTrainingHub]

🙏😊Glad you enjoyed it!

[@chrism9037]

Very clever and helpful, thanks Mynda! So awesome

[@MyOnlineTrainingHub]

Cheers, Chris! 🙏😊

[@pc-doctor1416]

Makes my head spin. Amazing what can be done,

[@MyOnlineTrainingHub]

I know, right?! 😁

[@Acheiropoietos]

Nicely done, Mynda. Nicely done. 😎

[@MyOnlineTrainingHub]

Thank you!! 😁 🙏

[@brianvaddan9987]

Thank Mynda for the brilliant explanation. Have you ever tried to combine the scan and Byrow function to get an accumulator for each row like a running total. I’ve tried but without success. I’m trying to get the scan function to work across the array but on a row by row basis, once again without having to drag down….. that’s so 90’s after all 😂

[@MyOnlineTrainingHub]

Thanks for watching, Brian! I haven't used these new functions much, yet. The running total sounds like a great case for these functions. I'll keep it in mind for future videos.

[@ankursharma6157]

Loved It! Loved It!! Loved It!!! THANK YOU!

[@MyOnlineTrainingHub]

Thanks so much! 😁🙏

[@thestitchmenagerie]

I am still so confused and lost on these new formulas. I keep trying to see how I can add them to my reports, but I just can't. :( I have watched all of your power query videos and am in love with it!) Thanks for all the great videos and great, straight-forward explanations!

[@MyOnlineTrainingHub]

I say go with what's best for you. If it were me, I'd use Power Query and PivotTables to do this too 😉

[@tenamsb686]

Good evening, ma'am First of all, thank you for all of these amazing tutorials - they help me a lot. Actually I had a doubt which I failed to resolve by myself. I was wondering if it's possible to import multiple CSVs from a folder as different queries to power query at once without actually combining them? Thank you, any help will be highly appreciated.

[@MyOnlineTrainingHub]

Glad you find my videos helpful! No, you can't import multiple files from a folder as separate queries.

[@tenamsb686]

@@MyOnlineTrainingHub Thank you very much for the reply ma'am! I was able to stumble upon a solution for the same. I was trying anything that I could imagine out of anxiety to get my work done and I realised that we can attach a folder to ODBC and use use ODBC to load multiple CSVs from that folder as different queries into the power query. Funny enough, it's such a simple solution to such a big problem but it's no where to be found on the internet.

[@MyOnlineTrainingHub]

Interesting! Thanks for sharing 👍

[@t2p5g4]

Thank you! My head is still hurting, though.

[@MyOnlineTrainingHub]

😁 mine too!

[@joeswright86]

Great example of the newer functions. I’ve always used sumifs with two rows in the header dictating the start and end of the sum. Are the one formula methods faster? They look easier to audit.

[@MyOnlineTrainingHub]

Thanks, Joe! I haven't speed tested them, but they're not likely to be processed over huge tables, so I wouldn't have thought it would be noticeable.

[@msoffice6037]

Thank you very much for the great content!!!

[@MyOnlineTrainingHub]

Glad you enjoy it!

[@steven.h0629]

❤Thanks formula Kings, and Queen 👍😎✊

[@MyOnlineTrainingHub]

Cheers, Steven 🙏😊glad you enjoyed it.

[@Franky2307]

Thank you for the great video. I need the learn a lot more here. Could you suggest a simple formula to insert to your above formula select rows (which have dates), columns with values . The sum will sort dates from Jan-March into Q1, April-June into Q2 etc. Dont shoot me. I am a beginner. ps. I already use SUMIF to collate the dates. How to fit SUMIF into yours formula.

[@MyOnlineTrainingHub]

Glad you liked it 🙏 Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@goutamnayak5011]

Thank you so much mam Mam i want make career in Data analyst So is it better to learn ms Excel deeply all functions Not depend on copilot and other ai tools

[@MyOnlineTrainingHub]

Yes, absolutely learn the functions relevant to being a data analyst and don't rely on AI.

[@Miro_L]

You guys are from different planet, no idea what is going on… 😂😂😂😂😂 it sure looks amazing

[@MyOnlineTrainingHub]

Thanks for watching 😁

[@joewilson4599]

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

[@lbhibbert]

I believe that since May 2023, Excel365 does this when you highlight the portion of the formula you wish to evaluate.

[@MyOnlineTrainingHub]

Thanks, Joe! I use the F9 key to evaluate parts of the formula in the formula bar.

[@dmitrystrukov1682]

The good old Sumproduct (wrapped into makearray) worked for me =LET(monthstarts, SEQUENCE(1,4,0,3), monthends, SEQUENCE(1,4,2,3), source,B8:I12, MAKEARRAY(ROWS(source),COUNT(monthstarts),LAMBDA(a,b,SUMPRODUCT(source*(SEQUENCE(ROWS(source))=a)*((SEQUENCE(1,COLUMNS(source),0,1)=INDEX(monthstarts,1,b)))))))

[@MyOnlineTrainingHub]

Nice. Sergei had a couple of MAKEARRAY solutions too.

[@Zvertnie]

Delightful! 👏👏👏

[@MyOnlineTrainingHub]

Glad you think so! 🙏

[@weekendeutopia]

Hello @MyOnlineTrainingHub how about summarizing quarterly data to years? Thank you in advance!

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@rafiullah-zz1lf]

Thanks to peter and sergei..even when we know we cant grasp the concept any way😂

[@MyOnlineTrainingHub]

😁Thanks for watching!

[@ec185]

I love your videos. I really do. However, this time its a huge overkill. Add a simple calculation on the side for 3 months, copy and paste. No complicated array formulas and easily understood for next quarter’s review

[@MyOnlineTrainingHub]

I guess if you're happy to do the work each quarter, then you do whatever suits. 😊 If you prefer a one and done formula, then these are options to consider. Or...just use Power Query and PivotTables, which is my preference: kzbin.info/www/bejne/gImkq2RvpdSJm80

[@IamTheReaper911]

Love em ❤

[@MyOnlineTrainingHub]

🥰🙏

[@jamesnaftalin6103]

Had you thought of doing it like this? LET( quantity,$C5:$N9, mths,$C$4:$N$4, qtrs,ROUNDUP(MONTH(mths)/3,0), MMULT(quantity,TRANSPOSE(N(qtrs=SEQUENCE(4)))))

[@jamesnaftalin6103]

or even =LET( quantity,$C5:$N9, mths,$C$4:$N$4, qtrs,ROUNDUP(MONTH(mths)/3,0), MMULT(quantity,TRANSPOSE(N(qtrs=SEQUENCE(5))+N(SEQUENCE(5)=5))))

[@MyOnlineTrainingHub]

Nice, James! You are now the 3rd amazing formula writer I know 😁

[@SamHawkins261]

Like this solution a lot. Might also switch "MONTH(mths)" to "MATCH(mths,mths,0)" in case the financial year isn't January to December.

[@RussellAngus-g2b]

Can this also cope with those of us whose quarters do not fit into a calendar year - e.g. fiscal year Apr - Mar?

[@MyOnlineTrainingHub]

Yes, but you'd have to use a different formula to map the months to their respective quarters.

[@catherinechen2010]

AMMMMMAZING!

[@MyOnlineTrainingHub]

So glad you liked it, Catherine! 🙏😊 Peter and Sergei are exceptional.

[@WynstonaJackreece]

Is there any way to track the rows that have been removed from a table because of a duplicate id (while other columns in the row do not have duplicated data)? Thank you in advance!

[@MyOnlineTrainingHub]

Once data is removed in Excel you can use the Track Changes tool if you have M365 to see how cells have been changed: kzbin.info/www/bejne/jpfKdZmLertjnK8

[@Erni760803]

Awesome

[@MyOnlineTrainingHub]

Thanks for watching!

[@GeoEmperar]

Did you say Peter and Sergei sent you 8 formulas, yet am struggling to WRAPMYHEAD onto only one? But in your hands, we are gratefully covered! Thanks Mynda!

[@MyOnlineTrainingHub]

In the end it was more than 8 🤯too much fun for one person!😁

[@mogarrett3045]

well done thank you Mynda. You're having too much fun too. Love it

[@MyOnlineTrainingHub]

😁 thank you!

[@goutamnayak5011]

Mam please ans me Copilot 365 will available in soon Ms Excel Should I learn ms Excel all functions and advance ms Excel or I can do my all work by copilot 365 i am confused Please ans me

[@MyOnlineTrainingHub]

Great question. Copilot will certainly help with standard formulas, and possibly more complex ones, but it will only be available with Enterprise licenses, so you'd want to hope that you only ever work for a company that is paying for the top tier license fees. Of course, it will always be quicker to write your own formulas if you know how. I know if I were looking for an employee to hire, I'm going to choose one who doesn't need to solely rely on AI to do their job.

[@ahshew]

I found that the column number isnt supported by excel fot web

[@panksimus]

I would've used this "=SUM(OFFSET($C5:$E5,0,3*COUNTA($C$12:C$12)-3))" in cell C13 and dragged down and across.

[@MyOnlineTrainingHub]

Thanks for sharing!

[@AbdulRahman-em7pc]

Wow😮

[@MyOnlineTrainingHub]

😊🙏

[@nadermounir8228]

Brilliant video 📹 👏 Thank u for sharing this challenging formula with us.

[@MyOnlineTrainingHub]

My pleasure!

[@wilmarkjohnatty4924]

Seems very unintuitive to put this data horizontally. We tend to put the dimension that we are going to Add more data to in the rows - that is new quarters /months.

[@MyOnlineTrainingHub]

Not as an accountant. Accountants have their chart of accounts in the rows and time across the columns. But there are equivalent solutions if you have your data transposed in the file you can download from the link in the video description.

[@YvesAustin]

7:28😂

[@MyOnlineTrainingHub]

Glad I gave you a giggle, Yves 😁

[@excelling6955]

one way can be =LET(a,C5:N9, sa,BYROW(CHOOSECOLS(a,{1,2,3}),LAMBDA(s,SUM(s))), sb,BYROW(CHOOSECOLS(a,{4,5,6}),LAMBDA(s,SUM(s))), sc,BYROW(CHOOSECOLS(a,{7,8,9}),LAMBDA(s,SUM(s))), sd,BYROW(CHOOSECOLS(a,{10,11,12}),LAMBDA(s,SUM(s))), HSTACK(sa,sb,sc,sd))

[@MyOnlineTrainingHub]

Nice alternative! Thanks for sharing 😊

[@elisabethd9325]

This will be interesting to digest. How could I have flexibility to have a fiscal quarter/year. e.g., fiscal year is Oct - Sep and therefore Q1 would be Oct - Dec?

[@ricos1497]

I'm not at my PC just now unfortunately. However, you could use the MOD() function to determine the fiscal month number and then the same ROUNDUP() function as the video from there to get the quarter. Something like: =ROUNDUP((MOD(yourDateRange+3,12)+1)/3,0) I might have the +1 wrong, but if you play about with the two functions on their own, you'll get it.

[@Lost_in_southern_california]

Fancy.

[@MyOnlineTrainingHub]

Thanks for watching!

[@drsteele4749]

Mynda, don't bang your head - you could brain your damage!

[@MyOnlineTrainingHub]

😁

[@robparker1625]

=LET(MINDBLOWN) !

[@MyOnlineTrainingHub]

😁I know, right! Peter and Sergei's formulas are next level.

[@grahamparker7729]

Wowza, that was some formula….

[@MyOnlineTrainingHub]

Sure was, Graham! 😁 Thanks for watching.

[@mathijs9365]

The hard way...

[@MyOnlineTrainingHub]

Fair enough 😉

[@cz287cj]

cannnot use pivot table?

[@MyOnlineTrainingHub]

Did you watch to the end? I say that at the end, but the limitation with this data is it's not in a tabular format, so we have to unpivot it first with Power Query. Here's the video link: kzbin.info/www/bejne/gImkq2RvpdSJm80

[@cz287cj]

​@@MyOnlineTrainingHub i take it there is no record of original raw data.

[@goutamnayak5011]

Please mam ans me my last comment

[@MyOnlineTrainingHub]

I did.

[@patrickkinbonso1809]

I would be surprised if I manage to follow the formulas,.yes hurting my head

[@MyOnlineTrainingHub]

😁 I don't blame you, Patrick!

[@apollosht]

Why not just do a sum offset or add some helper rows and use sumifs. Even someone who is an expert at excel would take ages to figure out how to audit this formula and understand its mechanics without watching a 12 min video

[@MyOnlineTrainingHub]

If you look at Peter's file, you'll see he has simplified them into custom LAMBDA functions that are easy to use, even for beginners. If it's an auditor issue, then they will eventually have to get their skills up to speed, because these functions aren't going away.

[@rounakvarma3399]

Doesnt work man tried like a 100 times

[@MyOnlineTrainingHub]

Perhaps you don't have these functions in your version of Excel.

[@rounakvarma3399]

​@@MyOnlineTrainingHub perhaps.

[@the_afterthot]

that was fab! love your vids! and your defintion of fun!

[@MyOnlineTrainingHub]

Thank you so much!! 😁

[@jawadhassan4917]

[@MyOnlineTrainingHub]

😁