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

Copper Contributor

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

Hi,

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.

[ Eg: =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)) ]

Appreciate your help. Here is the data sample.

 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
4 Replies

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

In principal a PivotTable should do what you expect, it depends on how your data are stored on a sheet...

1. Could you post a reprensentative sample workbook?
(if not allowed to attach here yet, share with i.e. OneDrive, Google Drive...)
2. What version of Excel do you run?
3. On which OS (Windows, MacOS....)?

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

Hi Lz,

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

Thanks again.

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

In attached workbook the Range of cells I colored in yellow (only for you to understand/replicate):

is named BudgetData (see in Name Manager) - Eventually it can be formatted as Table (would be ideal) but I didn't want to change your current setup

This named range is transformed with Power Query so the Data Source for the PivotTable looks like:

Make sense? Any question let me know (I won't be much avail. in the next few days)

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

@Lorenzo Thank you!

I understood what you've done there. Works.

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.

Thanks,

guru