Income and Expense Report with One Formula

Spreadsheets Google Sheets
,
21

@ghsubs1

Here is the formula to sum the Income and Expenses items in Column D5:D…

=MAP(D4:D508, LAMBDA(item, 
  IF((item = "") + (item = " "),iferror(1/0), 
    LET(
      isExpense, (COUNTIFS(Categories!$A:$A, item, Categories!$C:$C, "Expense") + COUNTIFS(Categories!$B:$B, item, Categories!$C:$C, "Expense") + (item = "EXPENSE")) > 0,
      amt, IFERROR(
            SUM(FILTER(Transactions!$D:$D, 
                      (Transactions!$C:$C = item) + (Transactions!$E:$E = item) + (Transactions!$L:$L = item), 
                      Transactions!$A:$A >= $E$1, 
                      Transactions!$A:$A < $E$2+1)), 0),
      IF(isExpense, amt *-1, amt)
    )
  )
))

Some comments…

  • Start date is in E1; end date is in E2
  • The reason…IF((item = "") + (item = " "),iferror(1/0) is included because my Income and Expense list has a cell that has a space (" ") between the Income and Expense sections and I don’t want a value in that row
  • This multiplies Expenses by -1
  • This assumes you have Group (E), and Type (L) columns in the Transactions sheet, which they are not by default. If they aren’t, it won’t work. I have been able to use Query, Hstack, and lookup to create a virtual table with this info, but it slowed down the calculations on my machine. I can share if you want.
  • If you created a sheet to share, you would want to make the locations of the Category and Transaction columns dynamic.

Thanks.

22

I made another one formula sheet. I used Tiller’s new Holdings sheet and generated a “Holding Summary” sheet.

This sheet makes it easy to group, sort and summarize your holdings listed in the Holdings sheet.

See Holding Summary Sheet

Using just one formula for everything makes the sheet very fast and allows for dynamic formatting.