Budget Review sheet - Summarize expenses, income, and budgets by month or quarter

Spreadsheets gSheets Templates
32

@ghsubs1 If you’re still having trouble, please replace the formula in cell T3 with the following and let me know if it shows an error message:

=let(
  select1_,"Income",  select2_,"Expense",  subtotal_label_,AR9,  total_label_,AR10,  null_label_,AR11,  
  m_q_,V1,
  date_format_,"mmm yyyy",
  categories_months_,AV4:AV,
  current_,if(V1="Month",today(),if(mod(month(today()),3)=0,today(),eomonth(today(),3-mod(month(today()),3)))),
  start_,if(V1="Month",eomonth(current_,-columns(U3:AG3)),eomonth(current_,-columns(U3:AG3)*3)),
  categories_quarters_,query(transpose(categories_months_),"select quarter(Col1), year(Col1)",0),
  query_by_col_name_,
    LAMBDA(data_, query_, headers_,
      QUERY(
        {data_}, 
        LET(data_row1_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_)),
          REDUCE(query_, FILTER(data_row1_, ISTEXT(data_row1_)), 
            LAMBDA(query_result_, data_col_name_, 
              REGEXREPLACE(query_result_, "(?i)`" & REGEXREPLACE(data_col_name_, "([^a-zA-Z0-9 ])","\\"& "$1") & "`", "Col" & MATCH(data_col_name_, data_row1_, 0))))),
        headers_)),
  hidden_,iferror(query_by_col_name_(Categories!A:ZZ,"select `Category` where `Hide From Reports` = 'Hide' label `Category` '' ",1),""),
  selected_,lambda(select_,query_by_col_name_(Categories!A:ZZ,"select `Category` where `Type` = '"&select_&"' label `Category` '' ",1)),
  query_data_,
    iferror(query_by_col_name_(Transactions!A:ZZ,
     "select `Category`, `Amount`, `Month`, year(`Month`), quarter(`Month`)
      where `Month` >= date '"&TEXT(start_,"yyyy-MM-dd")&"' 
      "&ifna(join(" ",arrayformula(" and `Category` <> """&hidden_&""" ")),""),1),"QUERY_DATA_ ERROR"),
  pivot_,iferror(transpose(query(query_data_,if(m_q_="Quarter",
      "select Col4, Col5, sum(Col2) group by Col4, Col5 pivot Col1 order by Col4 desc, Col5 desc label Col4 '', Col5 '' ",
      "select Col3, sum(Col2) group by Col3 pivot Col1 order by Col3 desc label Col3 '' format Col3 '"&date_format_&"' "),1)),"PIVOT_ ERROR"),
  head_,iferror(if(m_q_="Quarter",arrayformula(CHOOSEROWS(pivot_,1)&" Q"&CHOOSEROWS(pivot_,2)),CHOOSEROWS(pivot_,1)),BYCOL(pivot_,LAMBDA(col_,"HEAD_ ERROR"))),
  separator_,BYCOL(pivot_,LAMBDA(col_,IFERROR(1/0))),
  data_,iferror(CHOOSEROWS(pivot_,sequence(rows(pivot_)-if(m_q_="Quarter",2,1),1,if(m_q_="Quarter",3,2),1)),BYCOL(pivot_,LAMBDA(col_,"DATA_ ERROR"))),
  selected_pivot_,lambda(select_,
      iferror(query(data_,"select * where "&join(" or ",arrayformula("Col1 = """&selected_(select_)&""" ")),0),BYCOL(pivot_,LAMBDA(col_,"SELECTED_PIVOT_ ERROR: "&select_)))),
  subhead_,lambda(select_,
      iferror(BYCOL(selected_pivot_(select_),LAMBDA(col_,if(col_=CHOOSECOLS(selected_pivot_(select_),1),UPPER(select_),""))),BYCOL(pivot_,LAMBDA(col_,"SUBHEAD_ ERROR: "&select_)))),
  subtotal_,lambda(select_,
      iferror(BYCOL(selected_pivot_(select_),LAMBDA(col_,if(col_=CHOOSECOLS(selected_pivot_(select_),1),UPPER(subtotal_label_&select_),sum(col_)))),
        BYCOL(pivot_,LAMBDA(col_,"SUBTOTAL_ ERROR: "&select_)))),
  subtable_,lambda(select_,iferror({subtotal_(select_);selected_pivot_(select_)},BYCOL(pivot_,LAMBDA(col_,"SUBTABLE_ ERROR: "&select_)))),
  subtotals_,iferror({subtotal_("Income");subtotal_("Expense")},BYCOL(pivot_,LAMBDA(col_,"SUBTOTALS_ ERROR: "&select_))),
  total_,iferror(BYCOL(subtotals_,LAMBDA(col_,if(col_=CHOOSECOLS(subtotals_,1),UPPER(total_label_),sum(col_)))),BYCOL(pivot_,LAMBDA(col_,"TOTAL_ ERROR"))),
  null_pivot_,selected_pivot_(""),
  result_,
    if(query_data_="QUERY_DATA_ ERROR",{query_data_,query_data_},
      if(pivot_="PIVOT_ ERROR",{pivot_,pivot_},
        {if(CHOOSECOLS(null_pivot_,1)="SELECTED_PIVOT_ ERROR: ",
                {head_},
                {head_;{null_label_,choosecols(null_pivot_,sequence(COLUMNS(null_pivot_)-1,1,2,1))};separator_});
           total_;separator_;subtable_(select1_);separator_;subtable_(select2_)})),
  result_)