Summarize Data: 3 parameters [Department, Expense, Months], Values as sum of 3 months.

Question

Hi,&nbsp;I have a table where monthly budgets are available based on expense heads and departments. I need to summarize this data for Year to Date analysis (eg. Total of Jan to Mar numbers). Columns should be expense heads (like COGS, Salary, Consultants), Rows should be departments (like Sales, Engg., Admin), and values shall be sum of Jan to March figures. Is there a way to do this easily? Currently I am using INDEX MATCH. But it is cumbersome as I have to keep extending the formula each month.&nbsp;[&nbsp;Eg:&nbsp;=INDEX($B$6:$N$11,MATCH($A40,$A$6:$A$11,0),MATCH($G$40,$B$4:$M$4,0))+INDEX($B$6:$N$11,MATCH($A40,$A$6:$A$11,0),MATCH($G$41,$B$4:$M$4,0))+INDEX($B$6:$N$11,MATCH($A40,$A$6:$A$11,0),MATCH($G$42,$B$4:$M$4,0)) ]&nbsp;Appreciate your help. Here is the data sample.&nbsp;Input Data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Budget - Expenses by DepartmentBudget($ Thousands)Jan-24Feb-24Mar-24Apr-24May-24Jun-24Jul-24Aug-24Sep-24Oct-24Nov-24Dec-24FY 2024Department: Engineering&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost of Goods Sold2002002002002002002002002002002002002400Salary Cost4004004004004004004004004004004004004800Consultants505050505050505050505050600Travel101010101010101010101010120Marketing55555555555560Other Expenses77777777777784Total6726726726726726726726726726726726728064&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Department: Sales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost of Goods Sold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Salary Cost3503503503503503503503503503503503504200Consultants55555555555560Travel505050505050505050505050600Marketing1001001001001001001001001001001001001200Other Expenses999999999999108Total5145145145145145145145145145145145146168&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Department: Admin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost of Goods Sold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Salary Cost1001001001001001001001001001001001001200Consultants252525252525252525252525300Travel55555555555560Marketing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Other Expenses151515151515151515151515180Total1451451451451451451451451451451451451740&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total Budget13311331133113311331133113311331133113311331133115972&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Requirement&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Budget Year to Date (YTD) - Eg: Jan to Mar 2024&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;January to March&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ParticularsEngineeringSalesAdminTotal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cost of Goods Sold600&nbsp;&nbsp;600&nbsp;Jan-24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Salary Cost120010503002550&nbsp;Feb-24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Consultants1501575240&nbsp;Mar-24&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Travel3015015195&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Marketing15300&nbsp;315&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Other Expenses21274593&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Total201615424353993&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

lorenzo · Answer

Hi&nbsp;gurubengaluru&nbsp;&nbsp;In principal a PivotTable should do what you expect, it depends on how your data are stored on a sheet...Could you post a reprensentative sample workbook?(if not allowed to attach here yet, share with https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07, Google Drive...)What version of Excel do you run?On which OS (Windows, MacOS....)?

gurubengaluru · Answer

Hi Lz,Thank you for the response.1. Here is the link to excel file: https://1drv.ms/x/s!AiZbUq129VyDhKN_O77QSSOqqIJnGA?e=Z6Qlwd2. I am using Office 365 subscription. (Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20084) 64-bit )3. OS: Windows 11Thanks again.

lorenzo · Answer

Hi&nbsp;gurubengaluru&nbsp;&nbsp;In attached workbook the Range of cells I colored in yellow (only for you to understand/replicate):&nbsp;is named&nbsp;BudgetData (see in Name Manager) - Eventually it can be https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370#:~:text=1%20Select%20any%20cell%20in%20the%20table%20from,displayed%20in%20the%20default%20table%20format.%20See%20More (would be ideal) but I didn't want to change your current setup&nbsp;This named range is transformed with Power Query so the Data Source for the PivotTable looks like:&nbsp;&nbsp;Make sense? Any question let me know (I won't be much avail. in the next few days)

gurubengaluru · Answer

Lorenzo&nbsp;Thank you!I understood what you've done there. Works.&nbsp;So, there's no way I can execute this without any Power Query + Pivot Table? Because I would prefer to achieve this through just formulas so that I can maintain the worksheet and do work on top of it instead of overriding workbooks with Pivot Table.&nbsp;&nbsp;Thanks,guru

lorenzo · Answer

Hi&nbsp;gurubengaluru&nbsp;&nbsp;(in attached file) On the Data sheet, see a 365 Formula (quick &amp; dirty work...) in G38&nbsp;that does what you exceptNot sure I understand why a PivotTable doesn't work for you given that Budget figures don't - theoretically - change every day...

Forum Discussion

Summarize Data: 3 parameters [Department, Expense, Months], Values as sum of 3 months.

5 Replies

Resources

Input Data

Budget - Expenses by Department	Budget
($ Thousands)	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	FY 2024
Department: Engineering
Cost of Goods Sold	200	200	200	200	200	200	200	200	200	200	200	200	2400
Salary Cost	400	400	400	400	400	400	400	400	400	400	400	400	4800
Consultants	50	50	50	50	50	50	50	50	50	50	50	50	600
Travel	10	10	10	10	10	10	10	10	10	10	10	10	120
Marketing	5	5	5	5	5	5	5	5	5	5	5	5	60
Other Expenses	7	7	7	7	7	7	7	7	7	7	7	7	84
Total	672	672	672	672	672	672	672	672	672	672	672	672	8064

Department: Sales
Cost of Goods Sold
Salary Cost	350	350	350	350	350	350	350	350	350	350	350	350	4200
Consultants	5	5	5	5	5	5	5	5	5	5	5	5	60
Travel	50	50	50	50	50	50	50	50	50	50	50	50	600
Marketing	100	100	100	100	100	100	100	100	100	100	100	100	1200
Other Expenses	9	9	9	9	9	9	9	9	9	9	9	9	108
Total	514	514	514	514	514	514	514	514	514	514	514	514	6168

Department: Admin
Cost of Goods Sold
Salary Cost	100	100	100	100	100	100	100	100	100	100	100	100	1200
Consultants	25	25	25	25	25	25	25	25	25	25	25	25	300
Travel	5	5	5	5	5	5	5	5	5	5	5	5	60
Marketing
Other Expenses	15	15	15	15	15	15	15	15	15	15	15	15	180
Total	145	145	145	145	145	145	145	145	145	145	145	145	1740

Total Budget	1331	1331	1331	1331	1331	1331	1331	1331	1331	1331	1331	1331	15972



Requirement
Budget Year to Date (YTD) - Eg: Jan to Mar 2024
January to March
Particulars	Engineering	Sales	Admin	Total
Cost of Goods Sold	600			600		Jan-24
Salary Cost	1200	1050	300	2550		Feb-24
Consultants	150	15	75	240		Mar-24
Travel	30	150	15	195
Marketing	15	300		315
Other Expenses	21	27	45	93
Total	2016	1542	435	3993