Hi there!
As per the help center, SUMIF and SUMIFS are currently not implemented. I was wondering if there’s a workaround to get these functions working in grist?
Here is an example I created showcasing exactly what I’m after, specifically the grid showing a summary of income/expenses over a period of time:
I was thinking of using a new “table” card widget that just has a new row for each of those formulas:
So my question is then… Is this possible? Is there a workaround that allows for using these functions?
EDIT: Just realized this is possible using simple if statements. Once I’ve figured it out, I’ll share formulas and mark solved.
Yes, please share what you found.
Generally, since Python in supported in Grist formulas, calculations lik SUMIF/SUMIFS are possible using Python list comprehensions. Typically you have a list of records first, e.g. the $group value in a summary table, or the result of lookupRecords(). Then you can do, for example:
SUM(r.Income for r in $group if r.Date >= DATEADD(TODAY(), days=-90))
1 Like
So, now I’ve made all the formulas, and sharing here.
Realizing this is kind of niche but I think it’s good to share the formulas so that if someone were to come across this later on, it might be useful to them.
Ledger = The table containing all incomes/expenses
Ledger has columns Date (Date of transaction), Month, Income, Expenses, Combined
Date: Current date
Month: $Date.strftime("%Y-%m")
Income: Income
Expense: An expense
Combined: A hidden column where Income and Expense is combined so everything is in 1 column.
First, I made a hidden $Months column (To track total months since start) with formula: len(set([row.Month for row in Ledger.all]))
The formulas:
Total Profit: SUM(row.Income for row in Ledger.all)
Total Expense: SUM(row.Expense for row in Ledger.all)
Total Combined: SUM(row.Combined_Income_Expense for row in Ledger.all)
Last 90d profit: SUM(row.Income for row in Ledger.all if row.Date >= DATEADD(TODAY(), days=-90))
Last 90d Expense: SUM(row.Expense for row in Ledger.all if row.Date >= DATEADD(TODAY(), days=-90))
Last 90d combined: SUM(row.Combined_Income_Expense for row in Ledger.all if row.Date >= DATEADD(TODAY(), days=-90))
Last 30d is same thing with 30, instead of 90 days.
avg monthly profit: $Total_Profit / $Months
avg monthly expense: $Total_Expense / $Months
avg monthly combined: $Total_Combined / $Months
last 90d avg monthly profit: $Last_90_days_Profit / 3
last 90d avg monthly expense: $Last_90_days_Expenses / 3
last 90d avg monthy combined: $Last_90_days_Combined / 3
