mitul45/expense-manager

Monthly records template (Google Sheets)

Closed this issue · 3 comments

jakubgarfield commented

It would be awesome to have a month by month view of records and a summary how much you spent in the given month.

Do you have an idea how to create a sheet with this functionality?

jakubgarfield commented

I came up with a simple solution so far on a summary sheet:

2017	4	₹55.00

Where A is hardcoded year, B is hardcoded months and C has the following formula:

=ARRAYFORMULA(SUMPRODUCT(1*(YEAR(Expenses!$A$2:A)=A1)*(MONTH(Expenses!$A$2:$A)=B1)*Expenses!$E$2:$E))
  • ❤️1
jakubgarfield commented

Another option is to do a query on the dataset

=QUERY(Expenses!A2:E,"Select A, D, E where (A>=date '"&TEXT(EOMONTH(TODAY(), -1) + 1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(EOMONTH(TODAY(), 0),"yyyy-mm-dd")&"')")
mitul45 commented

Thanks! This is great. Have updated the sample sheet.