Double Unary

A unary operation is an operation with only one operand (input). The double unary (also called a double negative) is an operation used to coerce TRUE FALSE values to ones and zeros in more advanced formulas, especially formulas that work with arrays.

For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is:

=SUMPRODUCT(--(B5:B9=C5:C9))

Working from the inside-out, the B5:B9 is compared to C5:C9 in a simple expression that creates an array of five TRUE FALSE values:

=SUMPRODUCT(--({TRUE;TRUE;FALSE;TRUE;FALSE}))

We want to count matches (TRUE values) but the SUMPRODUCT function will ignore non-numeric values, so we use a double unary to change the TRUE FALSE values to ones and zeros.

This works because Excel automatically coerces TRUE/FALSE to 1/0 during math operations. The first negative changes TRUE values to -1, and FALSE values to 0. The second negative simply changes -1 to 1. The result inside SUMPRODUCT looks like this:

=SUMPRODUCT({1;1;0;1;0})

With only one array to work with, SUMPRODUCT simply returns the sum of all items, 3.

Above: Using the FILTER function to return multiple matches on “red”.

In fall 2018, Microsoft announced a new feature in Excel called “dynamic array formulas”. In a nutshell, this feature allows formulas to return multiple results to a range of cells on the worksheet based on a single formula entered in one cell. This behavior is called " spilling " and results appear in a " spill range “. All results are dynamic – if source content changes, the results dynamically update to stay in sync.

To leverage this feature, Microsoft also released new dynamic array functions:

These functions make many long-standing and difficult problems in Excel much easier.

For details and examples, see: Dynamic Array Formulas in Excel

Dynamic Arrays are only available in Office 365 .