James has attended:
Excel Intermediate course
Excel Advanced course
Tables, filtering and summing
I have built a table which houses my domestic expenditure in the form of a bank statement. I have assigned each expenditure a code key to identify the type of activity that it is. For example - Orange phone bill - has the code in the cell adjacent to the dialogue of 'Com' and in the next cell is the actual amount leaving the account.
I have a budget for the month which covers a number of 'Com' items and this sits in another worksheet within the book. I want to be able to sum the actual expenditure that relates to all 'Com' activity and show that next to the budget to see whether I am under or over the budgeted amount. I have a total of 22 codes (categories) that are budgeted for.
This will involve in the cell next to the budget a lookup function that matches the code with the budgeted cod and picks up the months entire expenditure. I have struggled with Vlookup, Conditional sum and other logical functions but cannot get it to see the correct code, the expenditure and then give the required output next to the budget cell.
Any advice welcome.
Thanks
James
RE: Tables, filtering and summing
Hi James, thanks for your query. Bit difficult to advise you without seeing the spreadsheet but the crux of this will involve something along the lines of:
=SUMIF(A1:A4, "ABC", B1:B4)
...which adds any cells in column B if they have the keycode ABC in column A. If you are sending the information between worksheets I would recommend naming the ranges involved. You mention using a lookup, which implies you want one formula to cope with the entire sheet but as I understand it if, say, the keycode is COM then you want to add that value to the current total of COM values. You will still use SUMIF to do this. Let me know how you get on and drop me a line if you need any further assistance.
Hope this helps,
Anthony
