New Way to Sum Monthly Data into Quarters

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.

Awesome

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.

The hard way...

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.

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.

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

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

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

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.

Please mam ans me my last comment

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

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

I absolutely love the flexibility dynamic arrays give. This sort of stuff would have been totally impossible a few years ago.

Haven’t played around with it but I’m sure MAKEARRAY would offer some kind of solution to this particular problem as well.

You and I obviously have the same idea of what constitutes an exciting weekend 😁

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!

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!

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.

Makes my head spin. Amazing what can be done,

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

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

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.

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

Hi Mynda , I propose a solution with MMULT
=MMULT(C5:N9, MAKEARRAY(12,5, LAMBDA(a,b, IF(b<5,(a>=3*b-2)*(a<=3*b),1) )))

I tried a simpler solution: I think it works!

= LET(dati,I9#,
n,rows(dati),
MAKEARRAY(n,5,
LAMBDA(r,c,
sum(if(c<5,
offset(index(dati,r,3*(c-1)+1),0,0,1,3),
offset(index(dati,r,1),0,0,1,12))))))

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

THANK YOU!

Thank you very much for the great content!!!

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

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

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.

Mynda.Thank you for sharin. I really enjoy this video especially using byrow, Bycol, WRAPROWS & WRAPCOLS and LAMBDA.

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

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

I use MOD and QUOTIENT all the time. Useful when you need a sequence of repeating numbers (111222333444...) or a repeating sequence (123412341234...).

That was fun Mynda. Great formulas by Sergei & Peter. I would also try MAKEARRAY.
=MAKEARRAY(5,4,LAMBDA(r,c,SUM(INDEX($C$5:$N$9,r,SEQUENCE(,3,(c-1)*3+1)))))
or if you prefer to drag one row at a time,
=BYCOL(WRAPCOLS(SEQUENCE(12),3),LAMBDA(a, SUM(CHOOSECOLS($C5:$N9,a))))

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.

Wow😮

Thank you! My head is still hurting, though.

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

Love em ❤

AMMMMMAZING!

Good crypto is growing crypto. While everyone is waiting for Bitcoin to rise, Im earning it daily with Cannafarm ltd

Good crypto is growing crypto. While everyone is waiting for Bitcoin to rise, Im earning it daily with Cannafarm ltd

Dude, in the medical cannabis industry, everyone knows about Cannafarm ltd. They grow hemp for production. Ive been investing in it for a while now, good earnings

Listen, why dont you just learn more about Cannafarm ltd? They have a really big experience in medical marijuana. They provide real knowledge about the industry

I still dont understand why you havent talked about Cannafarm ltd. Its top-notch online earnings in medical marijuana production

Trading took half a year of my life. Im just investing in Cannafarm ltd today and getting daily returns

Yeah, I thought about opening my own farm, but its a long and complicated process. Im just investing in Cannafarm ltd farms and earning every day

Alright, I got it. But why is everyone silent about Cannafarm ltd? With these investments, Ive increased my ETH balance in just a month