## Explanation

In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named range **variance**. The first formula in F5 simply sums all variances with the SUM function.

```
=SUM(variance) // returns -175
```

### Sum absolute variances

The formula in F6 calculates the sum of absolute variances with the ABS function together with the SUMPRODUCT function:

```
=SUMPRODUCT(ABS(variance)) // returns 975
```

In this formula, ABS returns the absolute value of variances to SUMPRODUCT in a single array:

```
=SUMPRODUCT({25;150;200;225;50;100;25;75;0;75;50})
```

SUMPRODUCT then returns the sum, 975.

*Note: we use the SUMPRODUCT function here instead of the SUM function because SUMPRODUCT can handle many array operations natively without entering the formula in a special way. This means it will work in any version of Excel without special handling. See Why SUMPRODUCT for more information.*

### Count non-zero variance

The formula in F7 counts the number of absolute variances that are greater than zero (0):

```
=SUMPRODUCT(--(ABS(variance)>0)) // returns 10
```

In this formula, ABS returns the absolute values for all variances in an array as explained above:

```
{25;150;200;225;50;100;25;75;0;75;50}
```

A logical expression is used to check for variances greater than zero:

```
{25;150;200;225;50;100;25;75;0;75;50}>0
```

This returns an array of TRUE and FALSE values:

```
--{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}
```

The double negative (--) converts the TRUE and FALSE values to 1s and 0s and the result is delivered directly to the SUMPRODUCT function:

```
=SUMPRODUCT({1;1;1;1;1;1;1;1;0;1;1}) // returns 10
```

which returns a final result of 10.

### Count positive and negative variances

The formula in F8 counts the number of *positive* variances:

```
=SUMPRODUCT(--(variance>0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}>0))
=SUMPRODUCT({1;0;1;0;0;1;0;1;0;0;0})
=4
```

The formula in F9 counts *negative* variances:

```
=SUMPRODUCT(--(variance<0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}<0))
=SUMPRODUCT({0;1;0;1;1;0;1;0;0;1;1})
=6
```

### Count absolute variance greater than 100

Finally, the formula in F10 counts absolute variances greater than 100:

```
=SUMPRODUCT(--(ABS(variance)>100))
=SUMPRODUCT(--({25;150;200;225;50;100;25;75;0;75;50}>100))
=SUMPRODUCT({0;1;1;1;0;0;0;0;0;0;0})
=3
```

### Direct array operation

In the example shown, the variances in column D act as a helper column. However, you can calculate the variances directly in an array operation if needed with the same results. For example, to count positive variances, the formula in F8 is:

```
=SUMPRODUCT(--(variance>0)) // returns 4
```

This formula can be rewritten to calculate variance internally like this:

```
=SUMPRODUCT(--(C5:C15-B5:B15>0)) // returns 4
```

The named range **variance** can be replaced with C5:C15-B5:B15 in all formulas above.