Need an excel / google sheets guru

HippySunshine

HippySunshine

Senior Member
I'm trying to make myself a new expenses spreadsheet.
Keeping things simple for example purposes...

On each row I have:
date
category (data validation dropdown list: bills, office equipment, travel)
cost

On a separate tab (workbook) I want a totals section that totals the amount spent on each category.
So I need to get the total of bills by finding each row with that category and getting the cost cell...?

I'm sure this must be possible but I've never really expanded my knowledge on spreadsheets further than the basics, so would be stoked if anyone can help :) and I hope this makes sense.
 
If I'm understanding it correctly it should just be a case of (at least with excel) selecting the desired cell on the 'totals' page then pressing = followed by selecting the tab with the cost cell and then selecting the cost cell then press enter.

Once done it should look something like this in the ='tab name'!A1 where A1 is the cell number
 
No, sorry maybe I didn't explain properly.


This is a simplified version of my expenses:
DATECATEGORY (this is a data validation field with a dropdown to select a category)COST
01.01.2020Bills£10.00
01.01.2020Office Equipment£10.00
01.01.2020Travel£10.00
01.01.2020Bills£10.00
01.01.2020Bills£10.00

This is my totals spreadsheet:
BILLS TOTALTRAVEL TOTALOFFICE EQUIPMENT TOTAL
£30.00£10.00£10.00


So every time Bills appears, I want the Cost of that bill added to the total in my Totals table.
Does that make more sense?
 
I don't do my accounts like this so mine are a lot easier to code for this type of thing.

Think it would need to be an IF formula and in all honesty I'm not that familiar with them.
I'm thinking it would be something similar to
IF B2:B5 equals Bills then sum Cn but like I say I wouldn't be doing mine like this, I'd have had bills, travel and office equipment as their own tables or tabs or depending on how you're registered for accounts just in one lump....
 
the formula you need is this
=SUMIF(Sheet1!$B2:$B40, "Bills", Sheet1!$C2:$C40)
=SUMIF(Sheet1!$B2:$B6, "Travel", Sheet1!$C2:$C6)
=SUMIF(Sheet1!$B2:$B6, "Office Equipment", Sheet1!$C2:$C6)
 
You must log in or register to reply here.
Back
Top