To include multiple entities in the parameters of a function, we can create a list of values and use a semi-colon to separate each one.  The use of commas is also supported but not recommended since it is also the thousands separator in many locales.

In this example, the three accounts we want to include are separated by a semicolon.

Example: =ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100;10200;10300",,,"09-2019")

To include all entities of a range, we use a colon to denote that our values are the starting and ending values of the range.

Example: 

In this example, the range is defined as starting with 10100 and ending with 11500.

=ACCOUNTENDINGBALANCE("Demo", ACTUAL,,"10100:11500",,,"09-2019")

Example: 

We can also combine lists and ranges. In this example, we are retrieving the ending balance for accounts 10100 through 11300 and also including account 10600

=ACCOUNTENDINGBALANCE("Demo", "ACTUAL",, "10100:11300;10600",,,"09-2019")

Example:

The techniques can be applied to other parameters.  Here we see a combined total of each Account Class from AP through COGS:

=ACCOUNTENDINGBALANCE("Demo","ACTUAL","AP:COGS",,,,"12-2023")

To add all entities that match a pattern, we use a question mark (?) in the parameter of a function, to denote "any character". This operator can be used at any point in the pattern, such as "10???", "??999", and "1???0".

Example: 

In this example, three question marks follow the first two digits of the account number. The result will include only those accounts that begin with 10.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10???",,,"09-2019")

Example: 

In this example, the two question marks at the beginning of the account number will result in including only those accounts that end with 999.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"??999",,,"09-2019")

Example:  

In this example, the question mark follows the first two letters of some of our expense account classes, giving us the same total as we would get if we were to list each account class balance and add them all up.

=ACCOUNTENDINGBALANCE("Demo","ACTUAL","EX?",,,,$D$1)

There is a slight difference between Velixo Classis and Velixo NX when using the "?" operator. By popular request, Velixo NX has been updated to only return data where the ? symbol represents at least 1 character (while Velixo Classic returns data where the ? symbol represents 0 or more characters).  For example, if we consider the following list of subaccounts: Velixo Classic will consider that the ACU subaccount fits the ACU? filter, where Velixo NX will not (since it requires at least a single character to be represented by the ?) Classic NX Workaround: The recommended filter to use with both Velixo Classic and Velixo NX is "ACU;ACU?" to return all values starting with "ACU":

To add all available entities in the parameter of a function, we can use an asterisk (*).

In the example below, the asterisk specifies that every branch will be included. This is effectively the same as leaving the parameter empty.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*","09-2019")

Let's say, for example, that we want to calculate the balance of an account for every branch except a few branches. We can't use the ? wildcard for our branch (unless the names all our branch identifiers are of the same length), and it's tedious to write out a long list of the accounts. By using the * operator, we can easily include or exclude the branches we want.

Example: =ACCOUNTENDINGBALANCE("Demo","ACTUAL",,"10100",,"*;-ONEBRANCH","09-2019")

We can use the Subtractor operator with the Account, Subaccount, or Branch parameters to exclude certain values from a range in these GL/Accounting functions:

• ACCOUNTTOTALDEBITS

• ACCOUNTTOTALCREDITS

• ACCOUNTTURNOVER

• ACCOUNTBEGINNINGBALANCE

• ACCOUNTENDINGBALANCE

This operator can also be put to use in the following parameters found in many of the Project Module Functions:

• AccountGroup

• CostCode

• Project

• InventoryItem

• Branch

Subtracting multiple entities

Often, it is inefficient to list all the branches to include. It is possible to use the Subtractor operator to subtract many entities from a range.

In this example, we use the Subtractor to exclude the SERVEAST, SERVWEST, and PRODRETAIL branches:

Example: =ACCOUNTENDINGBALANCE("Demo", "ACTUAL",,"49300",,"*;-SERVEAST;-SERVWEST;-PRODRETAIL","12-2019")

Another example where this would be useful is in a project report where we want to calculate expenses and exclude specific item codes, as with"*;-TRAVEL".