I'm very new to Google App Script so please bear with me. I'm trying to create custom functions in Google Sheets to monitor monthly and weekly sales and expenses. I can do it with built-in formulas, but it's too time-consuming to edit every cell every time. Here is what the Google Sheet looks like.
For monthly accounting, I use the formula below (here it's for "Sales"). I use the sum of multiple SUMIFS
for the expenses since there are multiple criteria ("Supplies", "Staff", or "Operations").
=ARRAYFORMULA(SUMIFS('2019'!$F$2:$F,text('2019'!$A$2:$A,"MMM"),A3,'2019'!$E$2:$E,"Sales"))
For weekly accounting, I use another formula below. Here, I sum up all transactions (here "Sales") within a given range of dates (here Mar 31 2019
to Apr 6 2019
). As in above, I use the same principle of summing up multiple SUMIFS
functions for "Expenses".
Sales: =SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Sales")
Expenses: =SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Supplies")+SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Staff")+SUMIFS('2019'!$F$2:$F,'2019'!$A$2:$A,">="&date(2019,3,31),'2019'!$A$2:$A,"<="&date(2019,4,6),'2019'!$E$2:$E,"Operations")
How can I translate the formulas shown above into a custom function? I was trying to write something like this one: