I have a finance file that I am having trouble creating a formula to calculate categories based on the month the expense occurred.
If I could upload an example file, I would. However, understandably so, that is not allowed on new accounts (just wish I knew that before making the file). I will attempt to explain the set up best I can.
The document has three sheets, Overview, Income and Expense. Income is irrelevant for this.
The expense sheet has 4 columns, (A) Date, (B) What, (C) Category, (D) Amount.
On the overview page, I want to pull the total amount spent in each category per month.
I can calculate the grand total amount spent for a month with the following formula;
=SUMPRODUCT(MONTH(Expenses.$A$2:OFFSET(Expenses.$A$20,-1,,))=1,Expenses.$D$2:OFFSET(Expenses.$D$20,-1,,))
Simplified it looks like this, =SUMPRODUCT(MONTH(A2:A20)=1,D2:D20))
in normal words, the formula is basically; Output the sum of column D, but only include rows where column A equals 1 (January)
I can calculate the total for each category on the entire sheet with the following formula
=SUMIF($Expenses.$C$2:OFFSET(Expenses.$C$20,-1,,),"cat1",$Expenses.$D$2:OFFSET(Expenses.$D$20,-1,,))
simplified =SUMIF(C2:C20),”cat1”,D2:D20)
Lastly I can calculate the categories per month by manually setting the rows.
=SUMIF($Expenses.$C$2:$C$6,"Cat1",$Expenses.$D$2:$D$6)
simplfied; =sumif(C2:C6,”cat1”,D2:D6)
I have not figured out a formula that will look at the entire data set to calculate the monthly category totals.
What I need is Output the sum of column D, but only include rows where column A equals 1 (January) AND column C equals "cat1"