Google Spreadsheet Advanced Pivot Table Query

Question:

( by Lakedawgs )

I  feel like I over complicated my request below.

I am basically trying to see if it is possible to automatically break out the income and expense totals by month on the 'Total Budget' page in the attached spread sheet. 

Sheet "Expenses":

Sheet "Income":

This may be a lot harder than I realize, not sure,

Thanks much

Solution:

Have a look at the following screenshot:

I have the following formula in Cell A1:

=query(Arrayformula(iferror(if({1,0,0};choose(month('Income'!B5:B);"01 Jan";"02 Feb";"03 Mar";"04 Apr";"05 May";"06 Jun";"07 Jul";"08 Aug";"09 Sep";"10 Oct";"11 Nov";"12 Dec");'Income'!B5:D)));"select Col3,sum(Col2) where Col1<>'' group by Col3 pivot Col1 label Col3 'Inflows', sum(Col2) '' ")

I have the following formula in Cell A15:

=query(Arrayformula(iferror(if({1,0,0};choose(month('Expenses'!A5:A);"01 Jan";"02 Feb";"03 Mar";"04 Apr";"05 May";"06 Jun";"07 Jul";"08 Aug";"09 Sep";"10 Oct";"11 Nov";"12 Dec");if({0,1,0};'Expenses'!E5:E;'Expenses'!B5:B))));"select Col3,sum(Col2) where Col1<>'' group by Col3 pivot Col1 label Col3 'Outflows', sum(Col2) '' ")


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