Sum up cells based on values

You should make a PivotTable to organize your data.

The first step is to normalize your raw data. This means put it into a 3 column list, with the first column being the month, the second the category and the third the amount.

The second step is to use the normalized raw data to create a PivotTable.

The following code normalizes the data and creates a PivotTable on a newly added worksheet. Make sure the worksheet with raw data is active, then run the macro.

Sub Normalizer()
Dim rg As Range, rgDest As Range
Dim ws As Worksheet, wsDest As Worksheet
Dim i As Long, j As Long, n As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set wsDest = ActiveWorkbook.Worksheets.Add(after:=ws)
wsDest.Range("A1:C1").Value = Array("Month", "Category", "Amount")
j = 2
With ws
    For i = 2 To 36 Step 3
        Set rg = Range(.Cells(2, i), .Cells(2, i).End(xlDown))
        n = rg.Rows.Count
        wsDest.Cells(j, 2).Resize(n, 2).Value = rg.Resize(, 2).Value
        wsDest.Cells(j, 1).Resize(n, 1).Value = .Cells(1, i).Value
        j = j + n
    Next
End With
wsDest.ListObjects.Add(xlSrcRange, wsDest.Cells(1, 1).CurrentRegion, , xlYes).Name = "tbData"

MakePivotTable
End Sub

Private Sub MakePivotTable()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tbData").CreatePivotTable _
    TableDestination:="R2C6", TableName:="PivotTable"
With ActiveSheet.PivotTables("PivotTable")
    .AddDataField .PivotFields("Amount"), "Sum of Amount", xlSum
    .PivotFields("Category").Orientation = xlRowField
    .PivotFields("Category").Position = 1
    
    .PivotFields("Month").Orientation = xlColumnField
    .PivotFields("Month").Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Select allOpen in new window

expenses-raw-dataQ28239427.xlsm

You can use the SUMPRODUCT function.
=SUMPRODUCT((raw!$C$2:$C$200)*(raw!$B$2:$B$200=A2))

See attached workbook.

Flyster
expenses-raw-data.xlsx

It is worth noting that you have two rows in the PivotTable for Netflix. One row is for expenditures with "netflix" while the other is for "netflix " (note the space at the end). Likewise, you have both "gas" and "gass".

Ideally, you'll police your raw data before running the macro to fix this type of problem. If that is not possible, then sort your data by Category and fix the spelling in column B of the worksheet with the PivotTable. Then right-click the PivotTable and choose "Refresh".

@ byundt,

I had something come up, that's why I left "quick post." Thanks for posting the formula for me.

Experts-Exchange is trying to improve its ranking in Google searches, and we've identified blind links as one of the causes of lower ranking. That's why I wanted to make the correction.

Thanks for being so understanding about the issue.

Brad

@byundt:
This is phenomenal, Thank you.
On your suggestion, I corrected the netflix_ and the gass.
When I run the macro
Tools > Macro > Macros > Normalizer > Run
I get an error:
Runtime error 1004
Application Defined or Object defined error

Am I doing this incorrectly?
I am an OS X if it matters.

johnnyho_ is correct: the Normalizer macro assumes that worksheet raw is active. I use a Mac myself, so the code is definitely working.

If you want to try a formula approach, you may paste the following formula in worksheet Summary cell B2, then copy it across and down:
=IFERROR(1/(1/SUMPRODUCT((INDEX(raw!$B$2:$AJ$77,,MATCH(B$1,raw!$B$1:$AJ$1,0))=$A2)*INDEX(raw!$B$2:$AJ$77,,MATCH(B$1,raw!$B$1:$AJ$1,0)+1))),"")

The above formula requires Excel 2007 or later for IFERROR. If SUMPRODUCT returns 0, (1/SUMPRODUCT(....)) returns an error value and IFERROR traps it. If SUMPRODUCT returns a value other than 0, then 1/(1/SUMPRODUCT(....)) returns that value. As a result, the formula returns an empty string (looks like a blank), if there were no expenditures during a month for the specified category. Otherwise, it sums up the expenditures by category and month.

The bit with MATCH in the formula (two places) is to make sure that INDEX is returning categories and expenditures for the right month.The formula uses MATCH(....) to get the categories column for a given month, and MATCH(....) + 1 to get the expenditures column.

Note that the formula restricts the addition to rows 2 through 77. The Normalizer macro accomplishes the same thing by looking down in each column for the first blank cell. It appeared that rows in raw worksheet below that point may have been scratch space. If not, please adjust the formula accordingly (or remove the blank spaces before using the macro). It's OK if the rows in the formula extend beyond your actual data.

The formula is more complicated than the one suggested by Flyster, but doesn't need to be edited for each different month.
expenses-raw-dataQ28239427.xlsm

Ok, I understand the active sheet issue.

I've noticed that some data is missing. It may be the the bottom rows are not included in the calculations, like maybe rows 78-104