arrays - Is there a better function to use then copy-paste and manually needing to change Variables? Google Sheets
问题描述
I have this function to tally the specific spending within a certain category within a certain month/year, determined by a drop down. When I tried to do this with a query table or pivot table it would appear different each month depending on some categories having no spending. The only way I could get the same categories each month was to copy paste this formula next to all the categories and only change the WHERE Col2 = '"&CATEGORY CELL&"' I am asking if there is a better way of doing this without all the copy paste needs. I have included the formula below. Thank you!
=IFERROR(Query({Chequing!E8:E,Chequing!G8:G,Chequing!I8:I},"SELECT SUM(Col3) WHERE Col2 = '"&B4&"' AND YEAR(Col1) ="&L7&" LABEL SUM(Col3) '' ",1),0) + IFERROR(Query({'Credit Card'!E8:E,'Credit Card'!G8:G,'Credit Card'!H8:H},"SELECT SUM(Col3) WHERE Col2 = '"&B4&"' AND YEAR(Col1) ="&L7&" LABEL SUM(Col3) '' ",1),0)
example output table Category = B [1]: https://i.stack.imgur.com/xHcfI.png
