How to perform summation on Trial Balance P&L Accounts to calculate net income

[Taxguy03]

Hello, I need to verify the (income) loss by Entity by performing summation for any account that is greater than 399999 (basically any accounts that start with *4 to *8, the revenue and expense accounts). I have attached a sample stack Trial Balance. You can ignore accounts from *1 to *3.

I hope you can help me to build a workflow for this. 

[RolandSchubert]

Hi @Taxguy03 ,

you can do that by selecting the accounts you need (Filter tool with a condition selecting only accounts starting with '4' or '8') and use a Summarize tool (group by entity).

Attached a sample workflow. Let me know, if it works for you.

Best,

Roland 

[Taxguy03 · Accepted solution]

Thank you, Roland. I think I'm missing the *5, *6, and *7 series accounts. Basically, I need to  perform a summation on accounts that falls between *4 to *8.

Is there a way to write the formula so it captures all the account that sums that accounts from  400000 to 899999 ? Sorry for the follow-up. 

[RolandSchubert]

No problem, I've modified the condition a bit to reflect this.

[Taxguy03]

Thank you, Roland. As  always, you're the best. Thank you for helping the community. 

[Taxguy03]

Hi Roland, sorry to bother you again. I just noticed that within my range of accounts (400000-899999), I also have these two alpha account: 'ICPCOS', 'ICPRev' unlike the other accounts that are numeric value. 

How do I add accts 'ICPCOS', 'ICPRev' ?

I attempted the formula below but it seems to not work.

Left([Account], 1) IN ('4', '5', '6', '7', '8', 'ICPCOS', 'ICPRev')

[Taxguy03]

Hi Roland, sorry to bother you again. I just noticed that within my range of accounts (400000-899999), I also have these two alpha accounts: 'ICPCOS', 'ICPRev' unlike the other accounts that are numeric value. 

How do I add accts 'ICPCOS', 'ICPRev' ?

I attempted the formula below but it seems to not work.

Left([Account], 1) IN ('4', '5', '6', '7', '8', 'ICPCOS', 'ICPRev')

[RolandSchubert · Accepted solution]

Hi @Taxguy03 ,

the condition Left([Account], 1) IN ('4', '5', '6', '7', '8') takes the first character of the field [Account] and checks, if it's within the list 4,5,6,7,8.
To check additional accounts, you have to add a second condition - as you have two specific accounts to check, you can use:

[Account] IN ('ICPCOS', 'ICPRev')

A row is selected, if one of these conditions is true, so you have to connect them using "OR":

Left([Account], 1) IN ('4', '5', '6', '7', '8')
OR
[Account] IN ('ICPCOS', 'ICPRev') 

I've changed this in the attached workflow. 

Best,

Roland

[Taxguy03 · Accepted solution]

Thank you so much. You're the best. Saved me a lot of time.