How would you like to create complex, dynamic, fast performance Tiller sheets with useful reports using just one formula? I’ve been experimenting with a new way to build sheets with advanced formulas like LET() and some help from Google’s Gemini AI.
In the past, complex sheets would require a lot of hidden columns and helper formulas.
To show how this works, I’ll show you how you can build an Income And Expense report for any selected date range. You will get a Total Income, Total Expense and Net Income summary plus Income and Expense lists sorted by Amount.
If there is more interest, i’ll show some other one formula examples including a dynamic Transactions Report and more.
Rather than copying in a sheet, i’ll show you how to set it up with a few simple steps.
Setup
The approach requires setting up a new sheet and making sure your Transactions sheet has the columns it needs.
Create a New Sheet
- Create a new sheet in your Tiller Spreadsheet.
- In cell A1, name it “Income and Expense Statement.”
- In cell A2, type “Start Date.”
- In cell B2, enter a starting date, such as 1/1/26.
- In cell A3, type “End Date.”
- In cell B3, enter an ending date, such as 1/31/26.
Updating your Transaction Sheet
For the formula to work, you need to have these standard Columns in your Transactions Sheet: Date, Description, Category, Amount, and Account.
You will also need 2 extra lookup columns if you don’t have them already.
The column “Type” looks up the transactions Type (Income/Expense/Transfer) based on the Category.
The column “Group” looks up the transaction Group based on the Category.
If you already have Type and Group columns in your Transaction sheet, jump to the The Formula section below. If you don’t, add them using these instructions:
Check your Categories sheet to find the columns for Categories (usually A), Group (usually B), and Type (usually C). If your Categories sheet is different, map your column letters to where A, B, and C are in the formulas below.
If that’s a match, use this formula in row 1 in your Transaction sheet to the right of your existing columns for the new Type column.
=ARRAYFORMULA(IF(ROW(D:D)=1,"Type",XLOOKUP(D:D,Categories!A:A,Categories!C:C,"")))
Use with formula for your Group column.
=ARRAYFORMULA(IF(ROW(D:D)=1,"Group",XLOOKUP(D:D,Categories!A:A,Categories!B:B,"")))
The Formula
Now comes the key formula part. You don’t need to understand it but if you do, you (or Gemini AI) can build very useful custom sheets. I breakdown the formula later in this post if you are interested.
Put this formula in A5. (Make sure you copy the whole formula. You might need to scroll down in the formula to copy it all. If you cursor over the formula, you will see the Copy icon in the upper right. That’s the best way to copy it to your clipboard and then to the Google sheet.)
=LET(
start_date, B2,
end_date, B3,
h, Transactions!1:1,
data, Transactions!A:Z,
col_date, XMATCH("Date", h),
col_cat, XMATCH("Category", h),
col_amt, XMATCH("Amount", h),
col_type, XMATCH("Type", h),
get_col, LAMBDA(idx, INDEX(data, 0, idx)),
total_inc, SUMIFS(get_col(col_amt), get_col(col_date), ">="&start_date, get_col(col_date), "<="&end_date, get_col(col_type), "Income"),
total_exp, SUMIFS(get_col(col_amt), get_col(col_date), ">="&start_date, get_col(col_date), "<="&end_date, get_col(col_type), "Expense"),
net_inc, total_inc + total_exp,
c_cat_let, SUBSTITUTE(ADDRESS(1, col_cat, 4), "1", ""),
c_amt_let, SUBSTITUTE(ADDRESS(1, col_amt, 4), "1", ""),
c_date_let, SUBSTITUTE(ADDRESS(1, col_date, 4), "1", ""),
c_type_let, SUBSTITUTE(ADDRESS(1, col_type, 4), "1", ""),
summary, {
"Summary", "Amount";
"Total Income", total_inc;
"Total Expense", total_exp;
"Net Income", net_inc;
"", ""
},
income_list, QUERY(Transactions!A:Z,
"SELECT " & c_cat_let & ", SUM(" & c_amt_let & ")
WHERE " & c_date_let & " >= date '" & TEXT(start_date, "yyyy-mm-dd") & "'
AND " & c_date_let & " <= date '" & TEXT(end_date, "yyyy-mm-dd") & "'
AND " & c_type_let & " = 'Income'
GROUP BY " & c_cat_let & "
ORDER BY SUM(" & c_amt_let & ") DESC
LABEL " & c_cat_let & " 'Income by Category', SUM(" & c_amt_let & ") 'Total Amount'"),
expense_list, QUERY(Transactions!A:Z,
"SELECT " & c_cat_let & ", SUM(" & c_amt_let & ")
WHERE " & c_date_let & " >= date '" & TEXT(start_date, "yyyy-mm-dd") & "'
AND " & c_date_let & " <= date '" & TEXT(end_date, "yyyy-mm-dd") & "'
AND " & c_type_let & " = 'Expense'
GROUP BY " & c_cat_let & "
ORDER BY SUM(" & c_amt_let & ") ASC
LABEL " & c_cat_let & " 'Expense by Category', SUM(" & c_amt_let & ") 'Total Amount'"),
VSTACK(summary, income_list, {"", ""}, expense_list)
)
You should see the report.
I usually then remove the blank columns and rows (leaving some extra rows) to increase performance.
Formatting Tricks
To make the sheet look better and easier to read, now add some formatting.
- Make the A1 title bold
- Give the dates (the only fields you need to change) in B2 and B3 a light green background.
- Highlight the Income by Category row by selecting selecting add Format > Conditional Formatting with these setting:
- Apply to range A10:B
- Format Rules: Custom formula is =$A10=“Income by Category”
- Formatting Style: Light green background
- Highlight the Expense by Category row, by changing the rules from “Income by Category” to “Expense by Category” and style it light red.
That method of highlighting the rows works even if you add or remove the number of categories you have. This makes the formatting rules dynamically adjustable.
Options
In this example, i didn’t add the Category sheet feature to Hide From Report certain categories. But that can be done by adjusting the formula.
Sometimes, Gemini added comments to the formula when I was improving it. While comments are helpful, they caused errors and broke the formula. It seems Google Sheets doesn’t support standard comments in these formulas and Gemini doesn’t know that.
Important Note
I haven’t tested this on many sheets so it is possible this might not work for everyone. But if you have the correct inputs, i think it will work.
The formula itself only requires these 4 inputs:
B2 - date
B3 - date
Transactions!1:1 - the list of Column Headers in your Transactions sheet row 1
Transactions!A:Z - your Transactions sheet. If you have less than Z columns, you might need to adjust the Z to your last column. I’ve tried it with less columns and it worked thought.
If you don’t have those inputs, it won’t work. Also, if it doesn’t find any transactions in the data range, you will see errors. (There are ways to change the formula to improve how those errors display but I haven’t done that yet.)
Formula Breakdown
If you want to understand the formula, here is a quick summary:
=LET(
start_date, B2,
end_date, B3,
h, Transactions!1:1,
data, Transactions!A:Z,
col_date, XMATCH("Date", h),
col_cat, XMATCH("Category", h),
col_amt, XMATCH("Amount", h),
col_type, XMATCH("Type", h),
get_col, LAMBDA(idx, INDEX(data, 0, idx)),
The above section sets up the data sources, for the start date, end date, Transaction row headers and all the transaction data. It also figures out where the Date, Category, Amount and Type columns are.
total_inc, SUMIFS(get_col(col_amt), get_col(col_date), ">="&start_date, get_col(col_date), "<="&end_date, get_col(col_type), "Income"),
total_exp, SUMIFS(get_col(col_amt), get_col(col_date), ">="&start_date, get_col(col_date), "<="&end_date, get_col(col_type), "Expense"),
net_inc, total_inc + total_exp,
This calculates the totals for income, expense and net income.
c_cat_let, SUBSTITUTE(ADDRESS(1, col_cat, 4), "1", ""),
c_amt_let, SUBSTITUTE(ADDRESS(1, col_amt, 4), "1", ""),
c_date_let, SUBSTITUTE(ADDRESS(1, col_date, 4), "1", ""),
c_type_let, SUBSTITUTE(ADDRESS(1, col_type, 4), "1", ""),
This figures out the column letter for those 4 columns so it can use them in the query formula below.
summary, {
"Summary", "Amount";
"Total Income", total_inc;
"Total Expense", total_exp;
"Net Income", net_inc;
"", ""
},
This creates the Summary section at the top.
income_list, QUERY(Transactions!A:Z,
"SELECT " & c_cat_let & ", SUM(" & c_amt_let & ")
WHERE " & c_date_let & " >= date '" & TEXT(start_date, "yyyy-mm-dd") & "'
AND " & c_date_let & " <= date '" & TEXT(end_date, "yyyy-mm-dd") & "'
AND " & c_type_let & " = 'Income'
GROUP BY " & c_cat_let & "
ORDER BY SUM(" & c_amt_let & ") DESC
LABEL " & c_cat_let & " 'Income by Category', SUM(" & c_amt_let & ") 'Total Amount'"),
This query gets the sorted Income list.
expense_list, QUERY(Transactions!A:Z,
"SELECT " & c_cat_let & ", SUM(" & c_amt_let & ")
WHERE " & c_date_let & " >= date '" & TEXT(start_date, "yyyy-mm-dd") & "'
AND " & c_date_let & " <= date '" & TEXT(end_date, "yyyy-mm-dd") & "'
AND " & c_type_let & " = 'Expense'
GROUP BY " & c_cat_let & "
ORDER BY SUM(" & c_amt_let & ") ASC
LABEL " & c_cat_let & " 'Expense by Category', SUM(" & c_amt_let & ") 'Total Amount'"),
This query gets the sorted Expense list.
VSTACK(summary, income_list, {"", ""}, expense_list)
)
VSTACK is used to create the different sections in the correct order. It also generates a blank line between the income and expense sections.
What’s Next
Give it a try. Let us know what you think in the comments. I’ll share some other examples if there is interest.
If you want to understand the formulas better, you can read up about the functions; LET and VSTACK.
