CODEWITHSUNDEEP
excelworksheet-function
Travis L
Travis L

Reputation: 681

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

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!

Upvotes: 7

Views: 35412

Answers (2)

T0t3sMcG0t3s
T0t3sMcG0t3s

Reputation: 320

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)

Upvotes: 15

xQbert
xQbert

Reputation: 35323

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

Upvotes: 3

Related Questions