 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have tried to google this but not getting very far.
Hope my description is clear!
Not quite sure which function to use? Some type of Lookp?
I export my bank transactions to a spreadsheet and then add a column in which I categorise the transaction to the type of expense it is, e.g. office, phone, training, medical etc.
AT the moment I am sorting the sheet by the category and then manually adding a sum function to add up the sub total of that category.
Is there a function that would identify the value in the row that corresponds to each category and output the sub totals to a table?
Many thanks!
You could cheat by using SUMIF and then adding a column with the value of "1" next to the category so that there's a corsponding value to next to each expense (be it Office, Phone, Training etc) to "count".
https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b
ETA: Someone much smarter than me will hopefully be along in a minute to give you the right answer.
Bit of a lazy answer but you could create a pivot table from the data then filter away.
Edit: or invoke a power Query on the data then manipulate it thereon in.
ETA: Someone much smarter than me will hopefully be along in a minute to give you the right answer.
I think the SUMIF will do it perfectly. Thanks.
From the tips on the page you linked:
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
Let's assume the bank amounts are in cells A2:A100
And your assigned categories are in cells B2:B100 (office, phone, etc.)
So create a summary table like so:
in cell D1 type the word "office"
in cell D2 type the word "phone"
in cell D3 type the word "training"
in cell D4 type the word "medical" and so on
in cell E1 type the formula
= SUMIF($B$2:$B$100,D1,$A$2:$A$100)
and copy down to E4
Is there a function that would identify the value in the row that corresponds to each category and output the sub totals to a table?
That's pretty much the working description of what a Pivot Table does.
Thanks all.
Just had a play with the pivot table, very straight forward and very flexible.
Although the SUMIF does the job fine, the pivot table has a bit more about it!
I'd recommend the pivot table too - a neater and more flexible solution than a bunch of sumifs
pivot tables rock. You can even pull individual values out later so they can be a great way of summarising data to use elsewhere in a spreadsheet