Formula for GL Totalling not working

(6) Share

Report

Posted on by AleAlonso

309

Good morning,

I have created four GL accounts with the below No:

TA-Current Liab_T
Loans_ET
Provisions_ET
Net Pension Liab_ET

I also need a Total GL with the Totalling formula below:

"TA-Current Liab_T" - "Loans_ET" - "Provisions_ET" - "Net Pension Liab_ET"

I have tried few things, but I keep getting errors. Does anybody know how to put it so it works?

Thanks

Alejandra

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Community Member on at

Like (4)

Report

Alejandra,

I first have to ask why you created the G/L Accounts with letters and not actual numbers within the Chart of Accounts structure. If you create them as actual numbers, within the COA range, you can create simple Totaling Formulas. Below is a screen shot of an example.

If you need more help send a pic of what you are trying to accomplish in your COA and we can reply back.

Hope this helps.

Thanks,

Steve

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Dallefeld 211 User Group Leader on at

Like (4)

Report

Steve is right numbers work much better and Begin/End Totals work super well.

You account numbers won't even sort well. If you're worried about data entry, don't you type the name and the account comes up....this is what I do for my accounting.

The sorting would be :

Net Pension...

Loans...

Provisions....

TA-Current...

If you used numbers:

100 TA-Current Liab_T $ -100

200 Loans_ET $ - 200

300 Provisions_ET $ 100

400 Net Pension Liab_ET $ - 300

Account 500 could be a Total Account Type 100+200+300+400, calculation -100+-200+200+-300 or $ - 400

Was this reply helpful? Yes No
CU07110451-0 8 on at

Like (3)

Report

Steve,

Using your example

What if I created an account 13500 called "Total Securities and Accounts Receivable"

How do I add the amount under accounts 12300 and 13400 and under the Totalling field?

I cannot use range

If I put 12300..13400, then the amount for Accounts Receivable would be counted twice

You can suggest that I put 12300..13350 instead

But what if I have a few other different totals I wanted to add?

How do I add without using range, ie. the double dots?

I tried using &, |, + but all these doesn't work

There could be multiple sub totals and a overall total

Such as a total for each category - motor vehicles, office equipment, furniture and fittings, renovation

And a overall total - fixed assets

So, how do we add selective totals and present it as a overall total?

Thanks.

Was this reply helpful? Yes No
Suggested answer

Gregory Mavrogeorgis 784 on at

Like (3)

Report
Hi,

You can’t do that subtraction in a G/L account’s Totaling—that field only adds ranges/lists (e.g., 1000..1099 or A|B|C). Minus signs and quotes aren’t allowed, so you get errors.
Do it in Financial Reports (Account Schedules) instead:

Open your account schedule (or create one).

Add rows for each account:

100 → TA-Current Liab_T

200 → Loans_ET

300 → Provisions_ET

400 → Net Pension Liab_ET

Add a Formula row:

500 → Formula: 100-200-300-400

Run the report—row 500 gives:
TA-Current Liab_T - Loans_ET - Provisions_ET - Net Pension Liab_ET.
Tip: If you just needed a sum inside the COA, use a Totaling like
TA-Current Liab_T|Loans_ET|Provisions_ET|Net Pension Liab_ET (adds them). Subtraction = account schedule only.

You can’t do that subtraction in a G/L account’s Totaling—that field only adds ranges/lists (e.g., 1000..1099 or A|B|C). Minus signs and quotes aren’t allowed, so you get errors.
Do it in Financial Reports (Account Schedules) instead:

Open your account schedule (or create one).

Add rows for each account:

100 → TA-Current Liab_T

200 → Loans_ET

300 → Provisions_ET

400 → Net Pension Liab_ET

Add a Formula row:

500 → Formula: 100-200-300-400

Run the report—row 500 gives:
TA-Current Liab_T - Loans_ET - Provisions_ET - Net Pension Liab_ET.
Tip: If you just needed a sum inside the COA, use a Totaling like
TA-Current Liab_T|Loans_ET|Provisions_ET|Net Pension Liab_ET (adds them). Subtraction = account schedule only.

If you found this helpful please mark it as verified

Was this reply helpful? Yes No