Quarterly calculation based on Month (MMM) name
Hi Team,
Looking for formula to calculate every 3 month sum (Quarterly).
I am trying to calculate the quarterly sum based on month (MMM) name given in A2. Sum by From JAN to MAR, APR to JUN......etc
I would like to calculate for year 2020, 2021, 2022.
Attached sheet FYR.
Regards
Thank You So much for your help.
Power Query
Try
=SUMPRODUCT(($B$2:$E$13)*($B$1:$E$1=H$1)*(MONTH($A$2:$A$13&0)>=(ROWS($1:1)-1)*3+1)*(MONTH($A$2:$A$13&0)<=ROWS($1:1)*3))
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
H2=IF(COUNTIF($G$2:$G2,$G2)=COUNTIF($G$2:$G$13,$G2),SUMIFS(INDEX($B$2:$E$13,,MATCH(SUBSTITUTE(H$1,"QTR","")+0,$B$1:$E$1,0)),$G$2:$G$13,$G2),"")
Copy across and down
Thank You Very Much...!!
You guys are amazing..... Reps already added..
Another way.
Since you have Office 365 this approach will work. See the attached:
In column L:to yield unique Q#sFormula:
=UNIQUE($G$2:$G$13)
Then in H2 filled down and across:Formula:=IFERROR(SUM(FILTER(INDEX($B$2:$E$13,,MATCH(--LEFT(H$1,4),$B$1:$E$1,0)),$G$2:$G$13=$L2)),"")
Dave
Thank You for your help @FlameRetired.
That's works great too. Appreciated for help.
You are welcome. Glad to help.
Thank you for the feedback, added rep and marking your thread Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks