Payee transaction report by category

Google Sheets
1

Hi. Is there a canned report, or can someone give me some advice on how to prepare a report listing transactions sorted bu payee description and category, so I can see what I am spending by each payee (and broken down by categories within the same payee. In effect I’m trying to take the data in the Category Rollup report and just respin it so I can see how much I’m spending by payee and within that by category. I’d like to possably do some further breakdowns by month but lets focus on the first thing first.
I guess the other thing is I’m trying to understand how to do this in 1 continuous report for all payees, rather than individually like it appears that you have to do for the Category report now
Thanks
Ken Goodman

1 Like
2

For the first issue, have you looked at Transaction Tracker? It lets you slice and dice your transactions just about however you’d like.

On the second issue, not sure I’ve seen a solution for that.

3

Hey @kgoodman16 ,

The following formula should give you a report of spending by payee by category, with subtotals and grand total, for the last 12 months, albeit without the nice formatting of the Category Rollup report.

Change 12 in the second line to however many months you want to report.

If you have an actual ‘payee description’ column on your Transactions sheet, change Description in the third line to the name of that column. Otherwise, you’ll need very clean values in the Description column for this to be useful, since the grouping is by each unique value in that column.

=let(
months_,12,
group_by1_,"Description",
group_by2_,"Category",
query_by_col_name_,
  LAMBDA(data_, query_, headers_,
    QUERY({data_}, 
      LAMBDA(query_text_, data_row1_,
        REDUCE(query_text_, FILTER(data_row1_, ISTEXT(data_row1_)), 
          LAMBDA(query_text_result_, data_col_name_, REGEXREPLACE(query_text_result_, "`" & data_col_name_ & "`", "Col" & MATCH(data_col_name_, data_row1_, 0)))))
      (query_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_))), headers_)),
hidden_accts_,IFERROR(query_by_col_name_(Accounts!E:ZZ,"select `Account` where `Hide` = 'Hide' label `Account` '' ",1),""),
hidden_cats_,IFERROR(query_by_col_name_(Categories!A:ZZ,"select `Category` where `Hide From Reports` = 'Hide' or `Type` <> 'Expense' label `Category` '' ",1),""),
transactions_,
  query_by_col_name_(Transactions!A:ZZ,
   "select `"&group_by1_&"`, `Amount`, `"&group_by2_&"`, `Month`
    where `Month` > date '"&TEXT(EOMONTH(TODAY(),-months_),"yyyy-MM-dd")&"' 
    and `Month` <= date '"&TEXT(EOMONTH(TODAY(),0),"yyyy-MM-dd")&"'"&
    IFNA(JOIN(" ",ARRAYFORMULA(" and `Account` <> """&hidden_accts_&""" ")),"")&
    IFNA(JOIN(" ",ARRAYFORMULA(" and `Category` <> """&hidden_cats_&""" ")),"")&
   "order by `Date` desc ",1),
transactions_table_,
  QUERY({CHOOSECOLS(transactions_,1),ARRAYFORMULA(-1*CHOOSECOLS(transactions_,2)),CHOOSECOLS(transactions_,3,4)},
   "select Col1, Col3, sum(Col2) group by Col1, Col3 pivot Col4",1),
subtotals_,
  QUERY(
    QUERY({ARRAYFORMULA(CHOOSECOLS(transactions_,1)&" TOTAL"),ARRAYFORMULA(-1*CHOOSECOLS(transactions_,2)),CHOOSECOLS(transactions_,3,4)},
      "select Col1, sum(Col2) group by Col1 pivot Col4",1),
    "select * offset 1",0),
subtotals_table_,
  {CHOOSECOLS(subtotals_,1),MAKEARRAY(ROWS(subtotals_),1,LAMBDA(r_,c_,"")),CHOOSECOLS(subtotals_,SEQUENCE(1,COLUMNS(subtotals_)-1,2,1))},
grand_total_,
  QUERY(
    QUERY({ARRAYFORMULA(-1*CHOOSECOLS(transactions_,2)),CHOOSECOLS(transactions_,4)},
      "select sum(Col1) pivot Col2",1),
    "select * offset 1",0),
grand_total_table_,
  {"GRAND TOTAL","",grand_total_},
{QUERY({transactions_table_;subtotals_table_},"select * order by Col1",1);grand_total_table_})
1 Like
4

Rob, Thank you very much. Can ou tell me what I do with the code? Do I just drop it in a blank sheet in my workbook or does it go somewhere specific. Thanks
Not Rob Howell from Utah are you? Best
Ken Goodman

5

Sure thing. Try pasting that formula into cell A1 on a blank sheet in your Tiller workbook. (You can paste it into any cell on any sheet in your Tiller workbook, but you’ll get a #REF! error if there aren’t sufficient empty cells below and to the right to accommodate the results.)

Nope, not from Utah.