Complicated SUMIF function
I am busy creating a financial tracking sheet and I'm trying to achieve a spending overview based on different account types for different months.
This is what I have achieved so far:
I have managed to formulate a certain sheet's cells (the sheet I use to enter my daily spending transactions) so that I can view spending for each ACCOUNT as well as spending by different MONTH. What I am struggling to achieve is to formulate another cell that will give me spending by account within a particular month.
The end result should be, one cell will give a particular month's total spending, while another cell/cells should give that same month's spending broken down further by account.
Example: March total spending = $10000
Credit Card = 2500
Debit Card = 3750
Cash Spending = 3750
In the above example this is the "SUMIF" formula I have used to get a month's spending:
=SUMIF(Register :: $A3:$A365,"<="&DATE(Register :: $E$1,3,31),Register :: $E3:$E365)-SUMIF(Register ::$A3:$A365,"<"&DATE(Register :: $E$1,3,1),Register ::$E3:$E365)
....and this is the "SUMIF" formula I constructed to get spending by account:
=SUMIF($Account,$B$9,Register :: $Amount)
THE BIG QUESTION THEN: CAN I SOMEHOW COMBINE THE TWO SUMIF FORMULAS TO GET A SINGLE RESULT?
I hope this question makes sense because I am convinced there is a way of doing it, I just can't figure this one out.
Thanks for any help!
This is what I have achieved so far:
I have managed to formulate a certain sheet's cells (the sheet I use to enter my daily spending transactions) so that I can view spending for each ACCOUNT as well as spending by different MONTH. What I am struggling to achieve is to formulate another cell that will give me spending by account within a particular month.
The end result should be, one cell will give a particular month's total spending, while another cell/cells should give that same month's spending broken down further by account.
Example: March total spending = $10000
Credit Card = 2500
Debit Card = 3750
Cash Spending = 3750
In the above example this is the "SUMIF" formula I have used to get a month's spending:
=SUMIF(Register :: $A3:$A365,"<="&DATE(Register :: $E$1,3,31),Register :: $E3:$E365)-SUMIF(Register ::$A3:$A365,"<"&DATE(Register :: $E$1,3,1),Register ::$E3:$E365)
....and this is the "SUMIF" formula I constructed to get spending by account:
=SUMIF($Account,$B$9,Register :: $Amount)
THE BIG QUESTION THEN: CAN I SOMEHOW COMBINE THE TWO SUMIF FORMULAS TO GET A SINGLE RESULT?
I hope this question makes sense because I am convinced there is a way of doing it, I just can't figure this one out.
Thanks for any help!
Macbook Pro, Mac OS X (10.5.8)