Small Change to a Budget Template

[Meagan10t]

Please see the attached image for reference. I am creating a budget tracker and hoping to formulate some of these expenses to group together. In the attached example, I have subscriptions and swag expenses shown. On the pie chart that shows the total amount spent from all accounts related to the overall budget, this is perfect. However, on the table chart, this is currently showing each item that is listed below, but I would like this to group all subscriptions together, and group all swag items together, to show an overall amount for each allocated budget. Does this make sense?

 

[OaklandJim]

Hey Meagan! Other smarter folks on the list might give you a normal built-in formula or pivot table approach but I could not get it. So, I built my own formula with VBA.

Option Explicit

Function SumTypes(prType, prTypesRange, prValuesRange)

    Dim rCell As Range
    
    Dim iRow As Integer
    
    For Each rCell In prTypesRange
        
        iRow = iRow + 1
    
        If rCell.Value = prType Then SumTypes = SumTypes + prValuesRange.Cells(iRow)
    
    Next rCell

End Function

That means that you'll need to convert your workbook to a macro enabled one if it is not already. Do a save as and specify macro enabled type. The code above is placed into a "code module" using the Visual Basic editor. On the menu select Developer => Visual Basic. Create a new Code Module and put the formula (code) in there.

HERE is a link to the workbook so you can see how it works.

 

[OaklandJim]

I knew that there would be a simple solution that does not require VBA. I used Excel's Sumproduct function.

AggreateCategories.xlsm
	B	C	D	E	F	G
1				Marketing Expenses
2				Subscriptions	Swag	Stuff
3				1,200	5,800	1,020
4
5	Type	Total
6	Subscriptions	400
7	Subscriptions	800
8	Swag	800
9	Swag	5000
10	Stuff	250
11	Stuff	770
Source

Cell Formulas
Range	Formula
E3:G3	E3	=SUMPRODUCT(--($B$6:$B$11=E2),$C$6:$C$11)