Hi I have a sheet ledger and a sheet trial balance
in my trial balance i have heading of accounts one being
Camps Out and this is the gsheet formula =SUMIF(Ledger!H:H,“Camps Out”,Ledger!E:E)
Does anyone know how to achieve this.
Thank you
Hi Alan!
I set this up your example here: Community # 5871 - Grist
The Ledger table has two columns, Account Name and Amount. The Trial Balance table has two columns, Account and Total.
The formula in the Total column is;
SUM(Ledger.lookupRecords(Account_Name=$id).Amount)
Let’s break this down.
(Ledger.lookupRecords(Account_Name=$id)
We use a lookupRecords() formula to lookup all records in the Ledger table where this account is listed in the Account Name column. You see we match Account Name to this row’s ID. This is because Account Name is a reference column and stores the row ID of the referenced record.
This does assume that you are using a reference column here. Your set up might be slightly different. If it is not a reference column, you would use (Ledger.lookupRecords(Account_Name=$Account). Be sure to update the column names in the formula to match what you have in your document.
Ledger.lookupRecords(Account_Name=$id).Amount
We use dot notation to specify what value we want for each record found using lookupRecords(). We want to know the value in the Amount column.
SUM(Ledger.lookupRecords(Account_Name=$id).Amount)
Finally, we use SUM() to sum all of the Amount values for the records found using lookupRecords().
Read more about references and lookups here: References and lookups - Grist Help Center
If you have any follow up questions, please let me know!
Thanks,
Natalie
Natalie
Thanks very much just what i was looking for.
Alan F