Sum help if possible

[Moray]

Hi folks I wonder if somebody could throw a bit of light on this to save me a wee bit of time and make it clearer.

I have a sheet called Mainledger and table TrialB

In TrialB I have columns Account,Credit and Debit and also a column with Account.

I would like to sum up all vales of say Credit with account name Subs or value of Debit with account Electric.

Im just trying to understand how to set it up and then do more myself when I get it.

Thanks for any of your time and help.

[dbaum · Accepted solution]

SUM(SELECT(TableB[Credit], [Account] = “Subs”))

[dbaum · Accepted solution]

SUM(SELECT(TableB[Credit], [Account] = “Subs”))

[Moray]

Thanks very much got it now.  How or what is the best way to display the result of the calculation cant seem to get it to display.  I tried with a virtual column.  I wanted to show the results on another view if possible.

Thanks

[dbaum · Accepted solution]

Put that expression in the App formula of a column--whether physical or virtual. Then, include that column in a view based on the table where you created the column.

For example, create a table of Accounts, with columns for Account, Credit Balance, and Debit Balance; in your TrialB table, make the Account column a Ref type column based on the Accounts table's source Account column. In this scenario, the expressions would be along the lines of the following.

SUM(SELECT(TableB[Credit], [Account] = [_THISROW].[Account]))

Or, if your TrialB table is actually the aggregating table that tallies together the multiple entries per account from the MainLedger table, then in TrialB columns for Credit Balance and Debit Balance use expressions like:

SUM(SELECT(MainLedger[Credit], [Account] = [_THISROW].[Account]))

Also, consider instead using a view's Grouping function. In a Table or Deck type view based on the table that has the multiple entries per account, select Account for the Group by property and then select "SUM::Credit" for the Group aggregate property.

[Moray]

dbaum

Sorry not quite with it.  You mean create a table called Accounts with columns Account,Credit Balance and Debit Balance.  So do I make the account column REF the mainledger table with all the data in it ?  What goes in the 2 balance columns?  Just the formulas.  Sorry not very bright.

Alan

[dbaum]

So far, it’s still unclear to me which table has the multiple entries per account that you want to sum and in which table you want to create the sum of credits and sum of debits for each account. Also, your original post mentions two “Account” columns in n the same table. To communicate your setup more clearly, I suggest post screenshots of the list of columns from each table and indicate where you’re trying to create sums. 

[Morayweb]

Hi Dbaum.

will have a play about with it later a lot to take in but starting to get a grip of it.  When I fathom it out I will let you know.  Thanks again for your help

[Moray]

Hi first one is the ledger and second trial balance which is the totals of the accounts.  Hope this is clearer.

Thank you

[Moray]

TrialB Table

[dbaum]

One basic question to confirm: Are you actually using AppSheet? Or are you working solely in Google Sheets?

Assuming you're using AppSheet and you want to be able to enter in Trial B a single row for each account and have the credit and debit sums calculated there, here's an expression for the App formula property of the Credit Balance column:

SUM(SELECT(Mainledger[Credit], [Account] = [_THISROW].[Accounts]))

If you don't have a master list of accounts in its own table, you should consider that in order to ensure consistency of spelling, capitalization, etc., in the Account column of the Mainledger table. That master list could be your Trial B table. In that case, configure the Account column of the Mainledger table to be a Ref type column based on the Trial B table, which is, in effect, your master Accounts table. Be sure that in the Trial B table the Accounts column is marked as the table's Label column.

On the other hand, if what you need is a running total as of each Mainledger entry, you could add "balance" columns to that table and use an App formula property like:

SUM(SELECT(Mainledger[Credit], AND([Account] = [_THISROW].[Account], [_ROWNUMBER] <= [_THISROW].[_ROWNUMBER])))

[Moray]

Hi yes using appsheet but as yet no trial balance ok will see how it goes and get back.

[dbaum]

I should have noted that if your Mainledger and Trial B tables are linked via a ref column (eg, ID), then the expression would need to use that column. That’s why my suggestion to post screenshots was intended to refer to screenshots of the column list from AppSheet—so the column types and any relationship between tables would be clear. Anyway, for instance:

SUM(SELECT(Mainledger[Credit], [Account] = [_THISROW].[ID]))