Sum cells in a column that have a specific value in a cell in their row

Question

My table is as follows...

Timestamp | Category   | Cost 
--------------------------------
...       | Shopping   | 5
...       | Charity    | 10
...       | Dining     | 20
...       | Mortgage   | 1000
...       | Dining     | 30
etc...

What I need is a formula for each category value that will get the sum of the cost column for rows that have that category. ie. total spending in that category that I can place in the "actual spending" cell in my budget table. The data is input with a google form so I have almost no power over formatting.

Thanks for your help!

score 14 · Accepted Answer · answered Sep 18 '12 at 01:06

You could use multiple SUMIF() functions to place these sums anywhere in the spreadsheet. Assuming Column A is TimeStamp, Column B is Category, and Column C is Cost:

Shopping -> =SUMIF(B:B, "Shopping", C:C)
Charity  -> =SUMIF(B:B, "Charity", C:C)
Dining   -> =SUMIF(B:B, "Dining", C:C)
Mortgage -> =SUMIF(B:B, "Mortgage", C:C)

score 3 · Answer 2 · answered Sep 18 '12 at 01:03

Two options I see here. Pivot table is by far the fastest and easiest in my opinion. (See option 1 in image.)

or

If you know all the categories and have a specific place you want them... See option 2. This uses the command SumIf (Conditional summing) where it uses the value in column J and uses aggregation to sum all costs together.

enter image description here

Sum cells in a column that have a specific value in a cell in their row

2 Answers2

Linked