Sunday, August 18, 2013

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


( 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.


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:
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: 



  1. hello Kishan,

    I could use some help with a google doc spreadsheet i'm creating and will be using to give to my accountant on a monthly basis. I have itemized all charges year to date from my American Express card statements. I was able to download all the data from I have gone through and itemized each expense. Now, I'd like to sort by expense type (office expense, vehicle expense etc) and give the total. I've been calculating by hand and know it could be done with some sort of procedure. Could you help me with this?

    1. Hi Kevin,

      Sure, I'll try my best to help you...
      But for that I would suggest to share your spreadsheet with me on my email ""
      and also send me mail with details what exactly you want me to do?