Google Spreadsheet MMULT Solution How to calculate monthly subtotals of transactions by category

Question:

( by cko )

I'm trying to make a spreadsheet where I can enter in categorized transactions on one sheet and then have a second sheet that takes those transactions and adds up monthly subtotals for 

each category.

I've put most of it together in this example doc, but am not sure how to do the monthly and category sums on the second sheet.  Can anybody help with a working formula that does this?

Hopefully, this can be done in a way that is not hardcoded to a particular year so I could just duplicate to start tracking a new year.

Thanks in advance.

Solution:

Have a look at the following screenshot of "2013 Itemized":

Have a look at the following screenshot of "2013 Summary":

in the above sheet, I have the following formulas in Cell 

C2 (for January):

=arrayformula(if(isblank(A2:A);iferror(1/0);mmult(sign(A2:A=TRANSPOSE(if(month('2013 Itemized'!A2:A)=1;'2013 Itemized'!E2:E;"")));'2013 Itemized'!D2:D)))

D2 (for February):

=arrayformula(if(isblank(A2:A);iferror(1/0);mmult(sign(A2:A=TRANSPOSE(if(month('2013 Itemized'!A2:A)=2;'2013 Itemized'!E2:E;"")));'2013 Itemized'!D2:D)))

J2 (for August):

=arrayformula(if(isblank(A2:A);iferror(1/0);mmult(sign(A2:A=TRANSPOSE(if(month('2013 Itemized'!A2:A)=8;'2013 Itemized'!E2:E;"")));'2013 Itemized'!D2:D)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive