Here is a problem we face very frequently. You have a list of values by months. And you want to find out the totals by Quarter. How do you go about it? Show
There are 2 options:
While option 1 is good for knowing the values, we need option 2 if you want the values to be fed to another report, chart or dashboard. Writing Formulas to Get Quarterly Totals from Monthly Data:First, understand a little math formula called ROUNDUP():ROUNDUP formula takes a number and rounds it up to the nearest fraction as specified by you. So for eg.,
Now, assuming your monthly data is in cells B4:C15, Our objective is to find Quarters from dates and then add up all items in Q1 against Quarter 1. We can get the month from a date using MONTH() formula. If we divide the month by 3 and then round the value up to nearest integer we will get the Quarter. So, A formula like So the final formula for calculating sum of all the sales in Q1 is How this formula works?Well, the portion Since SUMPRODUCT has magical powers, it just processes all these ranges of data without batting an eyelid. Download the example worksheet and play with this formulaGo ahead and download the example workbook and play with SUMPRODUCT formula to understand this better. How do you calculate Quarterly Totals from Monthly Data?Do you know a better way to do such calculation? How do you usually do it? Please share your tricks and ideas using comments. Share this tip with your colleagues Get FREE Excel + Power BI TipsSimple, fun and useful emails, once per week.
PrevPreviousSUMIF works in 2D too [quick tip] NextQuarterly totals when you have multi-year data [SUMPRODUCT again]Next Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Want an AWESOME Excel School made me great at work. 5/5 – Brenda FREE Goodies for you... 100 Excel Formulas List From simple to complex, there is a formula for every occasion. Check out the list now. 20 Excel Templates Calendars, invoices, trackers and much more. All free, fun and fantastic. 13 Advanced Pivot Table Skills Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here. Get started with Power BI Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch. Recent Articles on Chandoo.org Hi everyone… Let me start by wishing you all a very happy holiday season and stellar start to 2023. Here is our annual holiday card. [pictured left to right: Nishanth (son), Jo (wife), Nakshu (daughter) and Chandoo (me)] Range Lookup in Excel – How to lookup the pricing tier? [Formulas]#### in Excel cellHow to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]Best of the lot
Related TipsLearn Excel Range Lookup in Excel – How to lookup the pricing tier? [Formulas]Learn Excel #### in Excel cellLearn Excel How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)Learn Excel FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]Learn Excel Filter one table if the value is in another table (Formula Trick)Learn Excel Which Excel Formulas should you learn first?33 Responses to “Find Quarterly Totals from Monthly Data [SUMPRODUCT Formula]”
|