We have great interest in being able to consolidate our departments by region. The department codes are random and subject to change of region so a range of departments or masking isn't an option. I thought to take advantage of pointing and clicking and using the sum of selected departments function under departmental comparatives in smartsheet, but it isn't working for me, the sum always ends up as 0. Has anyone had success with this - if I could figure out what the formula should look like I would happily hardcode it. Also, are there any plans, or is there a current function that will allow me to assign random departments to a summary... Hoping for advice...

Thank you.
Sandra

Hi Sandra,

To sum a group of non-consecutive departments, there is a function called RngMerge that can be used to build the appropriate account key for you. There are more thorough examples in the Help file for how to do this, but generally:

- Put the accounts in some cell (say A12)
- Put the departments in some cell (say D4)
- Each acct/dept should be separated by commas (for example: '100, 300, 500)
- Use RngMerge to build all the acct-dept combinations
RngMerge(A12,D4)
- Use this in a formula to get the amount (say for YTD net changes)
=ADGET("gldata.Y",RngMerge(A12,D4))

If you used the Departmental Comparative function, you could also see an example of the RngMerge function in use. Also, version 8.0B of Adagio Ledger allows you to create a sum of selected departments when creating a 'Departmental Comparative' statement.

Hello Sandra,

With the current release of the financial reporter, there is a really fast way to sum non-contiguous departments. Drag the account group you want to analyze onto the sheet, and select "Departmental Comparative" from the format box. Choose the dpartments you want, and then select, "Sum selected departments", giving the resulting column an appropriate title. If you don't want the individual departments to show on your output sheet, then mark the columns "hide when print".